MTD Not Working Properly

[Nigel99]

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)?

[v-zhangti · Accepted solution]

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.

[Nigel99]

Thanks!! It worked!!

[Greg_Deckler]

@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
  __Return

You 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-TIT...

Also:

[Nigel99]

@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.

[Nigel99]

@Greg_Deckler  Able to advise further? Thank you in advance 

[Greg_Deckler]

@Nigel99 Just replace TODAY with MAX('Table'[Date]).