Prior Year Total Month with Relative Date Filter

[Anonymous]

I am building reports that use only the relative date filter in the Filters pane: “is in this year”.

I can get MTD, YTD for prior year but I need the Completed Prior Year Same Month Last Year.

I try PARALLELPERIOD but I get a huge number because the Dates table has future dates.

My Formula:

Sales_USD_PY_MONTH = CALCULATE(

FACT_Sales[Sales_USD_Net_Dis],

PARALLELPERIOD(BI_Calendar[Date],-12,MONTH))

FACT_Sales[Sales_USD_Net_Dis] is a calculated measure

I suspect my brain is slow today because I can see a simple filter or qualifying addition should do the trick. Maybe not.

Other Fields available:

Date_Sales (TRUE)  if a past “closed” date but all of prior year = TRUE

FACT_Sales[CalendarKey] is a date field and stops yesterday (only populates with data, not contiguous dates)

Thank you in advance and I mark as “Solved” and I give KUDOS!

[Anonymous · Accepted solution]

I figured it out. Maybe not the best but it works. 

The column BI_Calendar[Month_Sales] I added to the calendar is TRUE for months where there is any sale recorded. 

Future months are FALSE.

The Calendar table has future dates so the CALCULATE needs to know where to stop. This limits the months pulled in for SAMEPERIODLASTYEAR to the now current month

Sales_USD_MTDPYCurrent =

CALCULATE( [Sales_USD_Net_Dis] ,

CALCULATETABLE( DATESMTD ( SAMEPERIODLASTYEAR ( BI_Calendar[Date] ) ) ,

BI_Calendar[Month_Sales] = True ) )

[amitchandak]

@Anonymous , Try if these examples can help

last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Last year Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-12))

year

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

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

[Anonymous]

Good stuff but it didn't work. I added the Month Rank column to the Dates table and it ranks all months so MAX[Month_Rank] delivers a huge number. 

I do have a field Month_Sales that = TRUE if the month has sales. Future months are FALSE. Past year months are all TRUE.