Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Assuming you have the Date column sorted ascending:
Add column with formula: (Year(date) - Year("#LatestQueryStep"{0}[date}) * 12 + Month(date)
//JW
Tried to add this as a custom column getting an error
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.