Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Can anyone please explain, why this measure is not working as expected (MTD_on_measure)?
(There is a reaason why MTD is calculated this way)
These are the dax expressions:
TotalSales = SUM('Table'[Sales])
month_start (Measure) =
var latest_date = CALCULATE(max('Table'[Date]),ALLEXCEPT('Table','Table'[Date]))
var month_start_date = latest_date - day(latest_date) +1
return month_start_date
MTD_on_measure =
CALCULATE([TotalSales],filter('Table',[Date]>=[month_start (Measure)]))
MTD_not_on_measure =
CALCULATE([TotalSales],filter('Table',[Date]>=date(2019,12,1)))
check_equality =
date(2019,12,1) = [month_start (Measure)]
@Anonymous In case you want MTD_on_measure to be same as MTD_noy_on_measure please create a measure as per below
MTD_on_measure =
var latest_date = CALCULATE(max('Data'[Date]),ALLEXCEPT('Data','Data'[Date]))
var month_start_date = latest_date - day(latest_date) +1
RETURN CALCULATE(SUM(Data[Sales]),filter(ALL('Data'[Date]),[Date]>=month_start_date))
@Anonymous thank you for your comment.
I should have probably mentioned it in the post that In my report I have a date slicer that changes current date and therefore MTD needs to be recalculated, so ALL would not fit in my case. I found a solution by simply copying Date column and using it instead, however I was rather seeking for an explanation why it was not working to understand DAX better.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |