Date Table Month Number 1-36

[v_mark]

Once we have a date table. Normally when we do Month(date) it will give us numbers from 1 -12

Is there a way to make that continuous/extend like 1-36?

[mahoneypat · Accepted solution]

I use this formula to add a custom colum in the query editor that calculates the months from the current month.  You still get a month index but the zero value is the current month, which is useful in visual level filters to have a dynamic filter (e.g., also show the latest 6 months).

let
today = Date.From(DateTime.LocalNow()),
thisdate = [Date]
in
(12 * Date.Year(thisdate) + Date.Month(thisdate)) - (12 * Date.Year(today) + Date.Month(today))

You can adapt it to get your request with the following.  Replace Previous Step with your actual previous step name in the query editor (#"Changed Type", etc.).

let
firstdate = List.Min(#"Previous Step"[Date]),
thisdate = [Date]
in
(12 * Date.Year(thisdate) + Date.Month(thisdate)) - (12 * Date.Year(firstdate) + Date.Month(firstdate))

Pat

[JW_van_Holst]

Assuming you have the Date column sorted ascending:

Add column with formula: (Year(date) - Year("#LatestQueryStep"{0}[date}) * 12 + Month(date)

//JW

[v_mark]

Tried to add this as a custom column getting an error