Forum Discussion

Anonymous's avatar
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's avatar
    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
Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

That's great if you have a separate Date Table on the "Date" field, marked as a Date Table and it has a relationship to your fact table with 

your sales figures. For this example I'll call the Date Table "Dates". I'll call your fact table "Sales".

MTD Sales = CALCULATE([Total Sales], DATESMTD(Dates[Date])

PMTD = CALCULATE([Total Sales], DATEADD(FILTER(DATESMTD(Dates[Date]), DATES[Date]<TODAY()), -1,MONTH))

 

This will give you MTD to the day for comparison purposes.

 

I hope this helps!