0
votes

I have scenario where I have to filter sales for last 3 months based on MTD of current Month.

Ex: If Selected Month is March which has maximum date 11th (11-Mar-2021) . I'll need to calculate for Last 3 months excluding current Month i.e. Till 11th in each of Feb 2021 ,Jan 2021,Dec 2020.

I see that following can be achieved using time intelligence for previous month. Can anyone suggest how to filter MTD with respect to other months as well

3Month MTD Test = 
CALCULATE(SUM([Sales]), DATESINPERIOD('Date_Dim'[Date],LASTDATE(PREVIOUSMONTH(Date_Dim[Date])),-3,MONTH),Day(Date_Dimn[Date]<=DAY(TODAY()))
)
1

1 Answers

0
votes

I think PARALLELPERIOD function should do the trick for you: https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax .

I'm not sure how your data structure looks like, but in any case you first need to filter your date column only for dates in the current month. If you are not just using filter pane for it you can use DATESMTD function for example: https://docs.microsoft.com/en-us/dax/datesmtd-function-dax.

After that you can use this column and PARALLELPERIOD to get sales for the same period 1,2 and 3 months ago.