How to calculate last 12 months

[Anonymous]

Dear Altruists, 

I have a calender table which DAX functions is like below:

Calendar =

ADDCOLUMNS (

CALENDAR(DATE(1981,01,01),DATE(2080,12,31)),

"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 want to get last 12 months in my report always. what should I do. I am a beginner user in PBI DAX. 

I have got a solution https://community.powerbi.com/t5/Desktop/DAX-formula-to-return-data-for-last-6-months/td-p/50455

But I am not sure how do i add this in my existing calender table function. 

[themistoklis · Accepted 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
)

[KarenL7]

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!

[Anonymous]

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. 

[themistoklis]

@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)

[amitchandak]

@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