Forum Discussion
MTD Not Working Properly
Table:
| Date | Item |
| 1-Aug | Apple |
| 1-Aug | Orange |
| 1-Aug | Banana |
| 2-Aug | Apple |
| 2-Aug | Orange |
| 3-Aug | Apple |
There are 2 slicers:
Slicer1: Date: Single Selection
Slicer2: Item: Multiple Selection
DAX: To determine MTD count
TOTALMTD(COUNT(Table['Item']), DATEMTD(ALLSELECTED(Table['Date'])))
Case1: Slicer1: 2-Aug & Slicer2: Apple and Orange
DAX returns 4 (Correct)
Case2: Slicer1: 3-Aug & Slicer2: Apple and Orange
DAX returns 3. Expected output is 5. (3 Apples on 1/2/3Aug and 2 Oranges on 1/2Aug)
Seeking advice here as I am struggling hard throughout the day. How to get the MTD DAX to cater for 2 oranges in the past (1/2 Aug)?
Hi, Nigel99
You can try the following methods.
New table:Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))Measure = Var _table=CALCULATETABLE(VALUES('Table'[Item]),FILTER('Table',[Item]<>BLANK())) Return CALCULATE(COUNT('Table'[Item]),FILTER(ALL('Table'),[Date]<=MAX('Date'[Date])&&[Item] in _table))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Nigel99 Try:
Measure = VAR __Today = TODAY() VAR __StartOfMonth = DATE( YEAR( __Today ), MONTH( __Today ), 1 ) VAR __Table = FILTER( 'Table', [Date] >= __StartOfMonth && [Date] <= __Today ) VAR __Return = COUNTROWS( __Table ) RETURN __ReturnYou may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Also:
Greg_Deckler Thanks for prompt reply. I afraid that this did not work out. The DAX is dependent on date selection from date slicer instead of today date.
Greg_Deckler Able to advise further? Thank you in advance
Hi, Nigel99
You can try the following methods.
New table:Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))Measure = Var _table=CALCULATETABLE(VALUES('Table'[Item]),FILTER('Table',[Item]<>BLANK())) Return CALCULATE(COUNT('Table'[Item]),FILTER(ALL('Table'),[Date]<=MAX('Date'[Date])&&[Item] in _table))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks!! It worked!!
