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,
I'm trying to figure out a DAX measure formula and I've hit a wall, I'd appreciate some help!
I want to show the cumulative month to date sales, regardless of the current context. I have a slicer with different periods like "Current Week", "Current Month" etc. If I select "Current week" I have a cumulative sum which shows the cumulative totals for the week, I can drill down to day and see that, no problem.
I would like to have a month to date measure that takes the first and last dates of the context month, and gives me a cumulative sum, but the first row of the context has the total for the month up to that point.
Example:
If the Period is for instance "Financial Year" I would like this to work for each displayed month in the year, but historic months would of course show the same as the cumulative.
I have a date dimension and if it helps, I have FirstDateofMonth and LastDateOfMonth columns.
Thanks for your help!
Solved! Go to Solution.
I got it in the end:
Measure MTD = CALCULATE ([Sales], FILTER(ALL('Date Dynamic Period Dimension'),'Date Dynamic Period Dimension'[Date]>= MIN( 'Date Standard Dimension'[StartOfMonthDate])), FILTER (ALL ( 'Date Standard Dimension' ), 'Date Standard Dimension'[Date] >= MIN( 'Date Standard Dimension'[StartOfMonthDate] ) && 'Date Standard Dimension'[Date] <= MAX ('Date Standard Dimension'[Date]) ) )
If you follow the tutorial I mentioned above, you get a standard date dimension and a dynamic date dimension (M2M join between them), my slicer is using the dynamic dimension. So I calculated the MTD with filters to reference both date tables. I used the StartOfMonth and EndOfMonth columns in the standard date dimension, but I could do that in DAX too.
In this scenario, since you already have date column in your table, you can directly use TOTALMTD to get Month to date total. It will calculate up to current row date, no matter what you selected in slicer.
MTD = TOTALMTD(SUM('Table'[Amount]),'Table'[Date])
Regards,
I got it in the end:
Measure MTD = CALCULATE ([Sales], FILTER(ALL('Date Dynamic Period Dimension'),'Date Dynamic Period Dimension'[Date]>= MIN( 'Date Standard Dimension'[StartOfMonthDate])), FILTER (ALL ( 'Date Standard Dimension' ), 'Date Standard Dimension'[Date] >= MIN( 'Date Standard Dimension'[StartOfMonthDate] ) && 'Date Standard Dimension'[Date] <= MAX ('Date Standard Dimension'[Date]) ) )
If you follow the tutorial I mentioned above, you get a standard date dimension and a dynamic date dimension (M2M join between them), my slicer is using the dynamic dimension. So I calculated the MTD with filters to reference both date tables. I used the StartOfMonth and EndOfMonth columns in the standard date dimension, but I could do that in DAX too.
Hi,
No answer to this yet, I wonder if the question makes sense? This is driving me crazy so any help would be greatly appreciated! In my post I mentioned a date period slicer, to achieve this I followed this tutorial:
https://www.fourmoo.com/2016/09/07/create-dynamic-periods-for-fiscal-or-calendar-dates-in-power-bi/
Thanks
!D
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |