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.
Dear Altruists,
I have a calender table which DAX functions is like below:
Solved! Go to Solution.
@Anonymous
If you want to have a rolliwng dynamic 12 month calendar you can use the following formula:
Calendar =
var TodayDate = TODAY()
var LastYear = YEAR(TodayDate)-1
var LastMonth = MONTH(TodayDate)
var LastDay = DAY(TodayDate)
return
ADDCOLUMNS (
CALENDAR(DATE(LastYear,LastMonth,LastDay),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),
"CALENDAR YEAR", YEAR ( [DATE] ),
"MONTH NAME", FORMAT ( [DATE], "MMMM" ),
"MONTH NUMBER", MONTH ( [DATE] ),
"WEEKDAY", FORMAT ( [DATE], "DDDD" ),
"WEEKDAY NUMBER", WEEKDAY( [DATE],2 ),
"WEEK NUMBER",WEEKNUM( [DATE],21),
"DAY", DAY([DATE]),
"QUARTER", "Q" & TRUNC ( ( MONTH ( [DATE] ) - 1 ) / 3 ) + 1
)
@Anonymous
If you want to have a rolliwng dynamic 12 month calendar you can use the following formula:
Calendar =
var TodayDate = TODAY()
var LastYear = YEAR(TodayDate)-1
var LastMonth = MONTH(TodayDate)
var LastDay = DAY(TodayDate)
return
ADDCOLUMNS (
CALENDAR(DATE(LastYear,LastMonth,LastDay),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),
"CALENDAR YEAR", YEAR ( [DATE] ),
"MONTH NAME", FORMAT ( [DATE], "MMMM" ),
"MONTH NUMBER", MONTH ( [DATE] ),
"WEEKDAY", FORMAT ( [DATE], "DDDD" ),
"WEEKDAY NUMBER", WEEKDAY( [DATE],2 ),
"WEEK NUMBER",WEEKNUM( [DATE],21),
"DAY", DAY([DATE]),
"QUARTER", "Q" & TRUNC ( ( MONTH ( [DATE] ) - 1 ) / 3 ) + 1
)
I know that this was posted a while back - but just to say thanks as it solved an issue for me as I was looking to get 12 months worth of rolling data into my report and was able to use this calculated table in the model which works great thank you!
will it be like:
var LastMonth = MONTH(TodayDate) -11 or what else
cause i did not understand how do i get the last 12 months from your solution.
@Anonymous
The formula returns a table with dates from 05/02/2020 till 05/02/2021 (rolling 12 month)
MONTH(TodayDate) returns a number and in our case 2. I dont actually understand why you put -11, because the formula that you wrote will return -9.
The combination of the following variables return the same day last year (05/02/2020)
var LastYear = YEAR(TodayDate)-1
var LastMonth = MONTH(TodayDate)
var LastDay = DAY(TodayDate)
@Anonymous , Option one you can get relative date slicer
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
Option rolling 12 month formula
example with date table
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
In case you need display 1 month based on selected date
https://www.youtube.com/watch?v=duMSovyosXE
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.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |