DAX calculation for MTD -- with a small twist

[mandar]

Hi,

I need to be able to write a DAX logic which would give me the MTD sales on any given day. Below logic works like a charm

Sales = CALCULATE(SUM('Fact'[Sales]), DATESMTD('Date'[Date]))

However, the catch is that the 1st of every month should show the MTD Sales of previous month. Going by the current logic it would just give me Sales for that day which is the 1st.

Any suggessions please.

[Greg_Deckler · Accepted solution]

@mandar You could do something like this:

Sales = 
  VAR __Date = MAX('Date'[Date])
  VAR __Result =
    IF(DAY(__Date) = 1,
      CALCULATE(SUM('Fact'[Sales]), PREVIOUSMONTH('Date'[Date])),
      CALCULATE(SUM('Fact'[Sales]), DATESMTD('Date'[Date]))
    )
RETURN
  __Result

That said, the TI functions are slow.

[mandar]

@Greg_Deckler  - Awesome, work amazingly well!! Thank you so much!! 🙂