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:

Please see example on page 2 of the attached. (PMTD)Here there are two measures to obtain prev mtd. 

Here is the link with example'

https://drive.google.com/file/d/1xS9kCnRn-y8Q-6wx5nLdZKeX9RgVURp4/view?usp=sharing 

 

PMTD = CALCULATE([amount], DATEADD(FILTER(DATESMTD(Dates[Date]), DATES[Date]<TODAY()), -1,MONTH))
Prev MTD = CALCULATE([Amount],
DATEADD(FILTER(DATESMTD(Dates[Date]), Dates[Date]<TODAY()), -1,MONTH))
 
On Jan 1 2020 =9
On Jan 2 2020 =18
Then look at result:(Scroll down) and the measures are blank until Feb 1 2020  =9
                                                                                                          Feb 2 2020= 18
To answere your question it keeps track of the exact day for calculating the result.
 

Please consider marking as solution if this works for you.
 
Thanks.