Creating the next 30/60/90/120.... day column in Power BI

Power Query M Code Showcase
1

Hi Everyone,

There is an article that addresses creating the past 30/60/90 days column. However, I want to add the next 30/60/90 days to the M-Code solution.

Attached is the pbix solution to the creating 30/60/90 solution

Thanks.
EDNA 30-60-90 Slicer.pbix (1.8 MB)

2

Hi @Godzy,

Give this a go:
I have assumed, as you did not specify it, that a “next period” start from “tomorrow”

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Today", TodaysDate, TodaysDate, 1},
                {"Yesterday", Date.AddDays(TodaysDate,-1), Date.AddDays(TodaysDate,-1), 2},
                {"Last 30 Days",  Date.AddDays(TodaysDate,-30), TodaysDate, 3},
                {"Next 30 Days",  Date.AddDays(TodaysDate, 1), Date.AddDays(TodaysDate, 30), 4},
                {"Last 60 Days",  Date.AddDays(TodaysDate,-60), TodaysDate, 5},
                {"Next 60 Days",  Date.AddDays(TodaysDate, 1), Date.AddDays(TodaysDate, 60), 6},
                {"Last 90 Days",  Date.AddDays(TodaysDate,-90), TodaysDate, 7},
                {"Next 90 Days",  Date.AddDays(TodaysDate, 1), Date.AddDays(TodaysDate, 90), 8}
                                   
             },
    fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
        let
            DayCount = Duration.Days(EndDate-StartDate)+1,
            DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
            AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
            CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
        in
            CreateTable,
    
    GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
in
    Output

I hope this is helpful

2 Likes
3

@Melissa, thank you for your care and feeding of all things of Time Intelligence and in particular the Extended Date Table, and sharing practical utility patterns such as this.

1 Like
4

Yes, it works :grimacing:, and thank you for the swift response.

5

Glad this worked well for you @Godzy, please keep in mind that it is up to you to also include an additional day in the returned range - let me illustrate by adding 31 here:
{“Next 30 Days”, Date.AddDays(TodaysDate, 1), Date.AddDays(TodaysDate, 31), 4},

@ystroman, this technique is attributed to Chris Webb, I just feel comfortable enough customizing it to fit specific user requirements and turning it into a “single query” solution.
It’s always a pleasure to help, when time permits :grinning:

1 Like