Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

MTD FOR PREVIOUS PERIOD

I would like to be able to compare my MTD sales with any previous month for the same date range. Currently, I can only see MTD sales for the current month. When a previous month is chosen, the Total ...

tamerj1

4 years ago

Hi @ganenthra94

Here is the formula modified for your case. You need also to use the month name in the visual instead of the year (from the pevious date table). However this won't work with my samp[el file as it's data has only monthly ganularity. So please try with your data.

MTD =
VAR NumOfMonths = -2
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR PreviousDates =
    FILTER (
        DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, NumOfMonths, MONTH ),
        DAY ( 'PreviousDate'[Date] ) <= DAY ( ReferenceDate )
    )
VAR Result =
    CALCULATE (
        SUM ( 'Sales'[Salesl] ),
        REMOVEFILTERS ( 'Date' ),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP ( 'PreviousDate'[Date], 'Date'[Date] )
    )
RETURN
    Result

tamerj1

Community Champion

4 years ago

Hi Anonymous

you can tey something like this https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-Cumulative-Total-Over-last-3-Year-based-on-Selected-Year/m-p/2544240#M71758

basically you need a previous date table which is just a copy of the existing date table. Then set the inactive relationship as indicated. You need to change the period of PARALLELPERIOD to 1 month

please let me know if you need any further help.

Anonymous
Not applicable
4 years ago
What do you mean change the period of PARALLELPERIOD to 1 month? Could not find it in the link you shared. Thanks once again.
- tamerj1
  Community Champion
  4 years ago
  Hi @ganenthra94
  Here is the formula modified for your case. You need also to use the month name in the visual instead of the year (from the pevious date table). However this won't work with my samp[el file as it's data has only monthly ganularity. So please try with your data.
  MTD = VAR NumOfMonths = -2 VAR ReferenceDate = MAX ( 'Date'[Date] ) VAR PreviousDates = FILTER ( DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, NumOfMonths, MONTH ), DAY ( 'PreviousDate'[Date] ) <= DAY ( ReferenceDate ) ) VAR Result = CALCULATE ( SUM ( 'Sales'[Salesl] ), REMOVEFILTERS ( 'Date' ), KEEPFILTERS ( PreviousDates ), USERELATIONSHIP ( 'PreviousDate'[Date], 'Date'[Date] ) ) RETURN Result
  - Anonymous
    Not applicable
    4 years ago
    Will try but would this enable me to compare with any other month? tamerj1
tamerj1
Community Champion
4 years ago
Anonymous
My mistake. I mean DATESINPERIOD and to change the period to -2 month
DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, -2, MONTH )