Forum Discussion
Prior Year Total Month with Relative Date Filter
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!
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 ) )
3 Replies
- amitchandak
Super User
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)) 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 ) )
