@PaulDBrown Sorry but I'm caught here.
I know I've done a filter with AND but I can't remember.
I believe I need to filter with AND BI_Calendar[DateSales] = TRUE to limit the dates to yesterday (last sales date).
I can not get prior year MTD. All the posts addressing this issue show SAMEPERIODLASTYEAR as the Dax function to use. None work for me. It delivers Total Prior year month.
Please help, thank you. I give KUDOS and mark as solved
It’s a daily report so RELATIVE DATE filter is wanted (In Current Year)
ABOUT THE DATA
Direct Query (very large company)
BI Calendar came from RADACAD and I added custom columns
BI_Calendar has future dates
BI_Calendar has a column that marks if the Date has sales, BI_Calendar[DateSales] (TRUE/FALSE) this seems the key but I can't figure out the filter in the DAX
Fact_Sales[CalendarKey] ends the prior day, non-contiguous
NONE OF THESE WORK
This delivers nothing because the FILTER is in the wrong place I assume
CALCULATE( TOTALMTD( [Sales_USD_Net_Dis] , BI_Calendar[Date] ) ,
SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )
CALCULATE( [Sales_USD_MTD] , BI_Calendar[Date] ,
CALCULATE( [Sales_USD_MTD] , FACT_Sales[CalendarKey] ,
CALCULATE( TOTALMTD( [Sales_USD_MTD] , BI_Calendar[Date] ) ,
Last year Same month = CALCULATE([Sales_USD_NET_Dis] ,
DATESMTD(DATEADD(BI_Calendar[Date] , -1,YEAR)) )
Go to Solution.
Found a solution from a site that I found and like:
The solution was a new calendar entry that needs to be a Measure, not a column. I had a column that did the same thing but it didn't work.
The Calendar Measure:
Then the Sales Measure can be made:
View solution in original post
I've been using the following pattern for similar situations. I'm not the original author, and unfortunately I don't remember where I got it from to properly credit her/him. Anyways, here it is:
RunningTotal2LY =VAR curYrMinDate = MIN( 'Time'[PK_Date])VAR curYrMaxDate = MAX( 'Time'[PK_Date] )VAR minDate = DATE( (YEAR( curYrMinDate ) -2 ),MONTH(curYrMinDate), DAY(curYrMinDate))VAR maxDate = DATE( (YEAR( curYrMaxDate ) - 2 ), MONTH(curYrMaxDate), DAY(curYrMaxDate))RETURNCALCULATE([Total Sales],FILTER(ALLSELECTED('Time'[PK_Date]),'Time'[PK_Date] >= minDate && 'Time'[PK_Date] <= maxDate))
One can substitute the value of 'curYrMaxDate' with the current date or let any filter context pass the values in for both 'curYrMaxDate' and 'curYrMinDate'. Hope this helps.
Click here to read more about the October 2021 Release!
Explore the latest tools,training sessions,technical expertise, networking and more.
Mark your calendars and join us for our next Power BI Dev Camp!