Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
@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).
PROBLEM
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
Sales_USD_MTDPY =
CALCULATE( TOTALMTD( [Sales_USD_Net_Dis] , BI_Calendar[Date] ) ,
SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )
Sales_USD_MTDPY =
CALCULATE( [Sales_USD_MTD] , BI_Calendar[Date] ,
SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )
Sales_USD_MTDPY =
CALCULATE( [Sales_USD_MTD] , FACT_Sales[CalendarKey] ,
SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )
Sales_USD_MTDPY =
CALCULATE( TOTALMTD( [Sales_USD_MTD] , BI_Calendar[Date] ) ,
SAMEPERIODLASTYEAR( BI_Calendar[Date] ) )
Last year Same month = CALCULATE([Sales_USD_NET_Dis] ,
DATESMTD(DATEADD(BI_Calendar[Date] , -1,YEAR)) )
Solved! Go to Solution.
Found a solution from a site that I found and like:
https://www.daxpatterns.com/standard-time-related-calculations/
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:
DatesWithSales =
Then the Sales Measure can be made:
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))
RETURN
CALCULATE(
[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.
Found a solution from a site that I found and like:
https://www.daxpatterns.com/standard-time-related-calculations/
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:
DatesWithSales =
Then the Sales Measure can be made:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |