Rolling 12 months for the prior year to the day

[Anonymous]

I've been asked for a rolling 12 months for the prior year (total) to compare to this year. These go to the exact day.

My little brain can't figure out out the date math. 

My current year 12 month rolling:

NetSales_12MO = CALCULATE (

Sum( FACT_Sales[NetSales] ) ,

DATESBETWEEN (

FACT_Sales[CalendarKey] ,

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( FACT_Sales[CalendarKey] ) ) ),

LASTDATE ( FACT_Sales[CalendarKey] )

) )

Thank you and I give Kudos!

[amitchandak · Accepted solution]

@Anonymous , Try with date table

// Rolling 12

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) //or
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH)) //or
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date],-12,MONTH))

// Rolling 12 before 12 

Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-12,month)),-12,MONTH))

//rolling 12 from last year end date

Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFYEAR(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))

Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

////Year on year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

[darentengmfs]

Hi @Anonymous 

Here is the formula I have in one of my report, please change the table name and the column name to whatever's in yours.

Last YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD(dateadd('DateTable'[Date],-1,Year),"12/31"))

YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD('DateTable'[Date],"12/31"))

YTD Difference = [YTD Sales] - [Last YTD Sales]

This should do the trick.