Anonymous

My feedback

  1. 46 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    7 comments  ·  Power BI Ideas » Other  ·  Flag idea as inappropriate…  ·  Admin →
    Anonymous supported this idea  · 
  2. 3,758 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  74 comments  ·  Power BI Ideas » Desktop  ·  Flag idea as inappropriate…  ·  Admin →
    Anonymous supported this idea  · 
    Anonymous commented  · 

    We have had two customers request this feature, just in the last week!

  3. 233 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    14 comments  ·  Power BI Ideas » Analytics  ·  Flag idea as inappropriate…  ·  Admin →
    Anonymous commented  · 

    The implementation of the github function (https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca) may be a little daunting. Here's code to generate a DateDim with the modified gethub function:

    /*
    based on <https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date>
    M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's
    `weeknum(x, 21)` doesn't give the correct ISO Week-Year.

    homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca>

    */

    let
    getISO8601Week = (someDate as date) =>
    let
    getDayOfWeek = (d as date) =>
    let
    result = 1 + Date.DayOfWeek(d, Day.Monday) //DPS - Use this to change First Day of week to Sunday: Date.DayOfWeek(d, Day.Sunday)
    in
    result,

    getNaiveWeek = (inDate as date) =>
    let
    // Sunday = 1, Saturday = 7
    weekday = getDayOfWeek(inDate),
    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
    ordinal = Date.DayOfYear(inDate),
    naiveWeek = Number.RoundDown(
    (ordinal - weekday + 10) / 7
    )
    in
    naiveWeek,
    thisYear = Date.Year(someDate),
    priorYear = thisYear - 1,
    nwn = getNaiveWeek(someDate),
    lastWeekOfPriorYear =
    getNaiveWeek(#date(priorYear, 12, 28)),

    // http://stackoverflow.com/a/34092382/2014893
    lastWeekOfThisYear =
    getNaiveWeek(#date(thisYear, 12, 28)),

    weekYear =
    if
    nwn < 1
    then priorYear
    else
    if nwn > lastWeekOfThisYear
    then thisYear + 1
    else thisYear,

    weekNumber =
    if nwn < 1
    then lastWeekOfPriorYear
    else
    if nwn > lastWeekOfThisYear
    then 1
    else nwn,

    // Padded version: week_dateString = Text.PadStart(Text.From(Number.RoundDown(weekNumber)),2,"0")
    week_dateString = weekNumber
    in
    // Text.From(weekYear) & "-W" & week_dateString & "-" & Text.From(getDayOfWeek(someDate)),
    week_dateString,
    /* let */
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([DateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([DateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([DateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([DateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([DateKey]), type number),
    #"Inserted Week of Year ISO" = Table.AddColumn(#"Inserted Week of Year", "Week of Year ISO", each getISO8601Week([DateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year ISO", "Week of Month", each Date.WeekOfMonth([DateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([DateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([DateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([DateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([DateKey]), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "YYWW", each Text.End(Number.ToText([Year]),2) & Text.End("0" & Number.ToText([Week of Year]),2)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each true)
    in
    #"Filtered Rows"

    Anonymous supported this idea  · 

Feedback and Knowledge Base

Ready to get started?

Try new features of Power BI today by signing up and learn more about our powerful suite of apps.