cancel
Showing results for
Did you mean:
Helper IV

## Date Table Month Number 1-36

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?

1 ACCEPTED SOLUTION
Microsoft

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

3 REPLIES 3
Microsoft

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Resolver III

Assuming you have the Date column sorted ascending:

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

//JW

Helper IV

Tried to add this as a custom column getting an error

Announcements