Power Query Date Functions Help

[Anonymous]

Hello!

I am trying to create a query that provides different time-intelligence periods (MTD, YTD, etc.). I have been able to get a few but am unable to figure out the remaining ones. Can you please help? Here is what I have so far, and those missing are further down. Thank you!!

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Today",
                TodaysDate,
                TodaysDate,
                1},
                {"Current Week to Date",
                Date.From(Date.StartOfWeek(TodaysDate)),
                TodaysDate,
                2},
                {"Current Month to Date",
                Date.From(Date.StartOfMonth(TodaysDate)),
                TodaysDate,
                3},
                {"Current Year to Date",
                Date.From(Date.StartOfYear(TodaysDate)),
                TodaysDate,
                5},
                {"Current Quarter to Date",
                Date.From(Date.StartOfQuarter(TodaysDate)),
                TodaysDate,
                4}
             },
    GetTables = List.Transform(Ranges,
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables),
    #"Sorted Rows" = Table.Sort(Output,{{"Sort", Order.Ascending}})
   
in
    #"Sorted Rows"

Missing (NEEDED)

• Same (Complete) Month Previous Year
• Same (Complete) Quarter Previous Year
• Previous Year
• Same Month (To Date) Previous Year
• Same Quarter (To Date) Previous year
• Previous Year (To Date)
• Past 30 Days
• Previous Month
• Previous Quarter

[Anonymous · Accepted solution]

I figured it out. New in red. Thanks!

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    ThisDayLastYear = Date.From(Date.AddYears(TodaysDate,-1)),
    Ranges = {
                {"Today",
                TodaysDate,
                TodaysDate,
                1},
                {"Current Week to Date",
                Date.From(Date.StartOfWeek(TodaysDate)),
                TodaysDate,
                2},
                {"Current Month to Date",
                Date.From(Date.StartOfMonth(TodaysDate)),
                TodaysDate,
                3},
                {"Current Year to Date",
                Date.From(Date.StartOfYear(TodaysDate)),
                TodaysDate,
                5},
                {"Current Quarter to Date",
                Date.From(Date.StartOfQuarter(TodaysDate)),
                TodaysDate,
                4},
                {"Past 30 Days",
                Date.AddDays(TodaysDate,-30) + #duration(1,0,0,0),
                TodaysDate,
                6},
                {"Past 60 Days",
                Date.AddDays(TodaysDate,-60) + #duration(1,0,0,0),
                TodaysDate,
                7},
                {"Past 90 Days",
                Date.AddDays(TodaysDate,-90) + #duration(1,0,0,0),
                TodaysDate,
                8},
                {"Past 120 Days",
                Date.AddDays(TodaysDate,-120) + #duration(1,0,0,0),
                TodaysDate,
                9},
                {"YTD Last Year",
                Date.From(Date.StartOfYear(ThisDayLastYear)),
                ThisDayLastYear,
                10}
             },
    GetTables = List.Transform(Ranges,
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables),
    #"Filtered Rows" = Table.SelectRows(Output, each ([Sort] = 10)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}})
in
    #"Sorted Rows"

[Anonymous]

I have been able to get the 30/60/90/120 Days but still cannot figure out the correct syntax for Previous Year, Month & Quarter and Previous Year. Month and Quarter to Date.

Any help would be greatly appreciated!

[...]

{"Past 30 Days",
Date.AddDays(TodaysDate,-30) + #duration(1,0,0,0),
TodaysDate,
6},
{"Past 60 Days",
Date.AddDays(TodaysDate,-60) + #duration(1,0,0,0),
TodaysDate,
7},
{"Past 90 Days",
Date.AddDays(TodaysDate,-90) + #duration(1,0,0,0),
TodaysDate,
8},
{"Past 120 Days",
Date.AddDays(TodaysDate,-120) + #duration(1,0,0,0),
TodaysDate,
9}

[...]

[camargos88]

Hi @Anonymous ,

I think you can solve all those fiels with measure on DAX, it's dynamic and don't need to create so many fields on your model.

Check this link: https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

Ricardo

[Anonymous]

Hi @camargos88 , thanks! I would love to, but I cannot figure out how to use these for just the Calendar table. What I am trying to do is create a Time Period slicer that has all the time periods listed below but that works for multiple pieces of data. For example, if I have a table that shows a client, their revenue, cost and profit, I want to select MTD, YTD, etc from the slicer and have each revenue, cost and proft change to MTD, YTD etc. All I have been able to figure out how to do currently is set up a DAX measure (and slicer) for Revenue only  or for Profit only , but not one that controls all data. I hope I make sense!