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 sales for that month is shown instead of the total sales for the same period in the current month. 

 

So for example, if the current MTD is the 1-28th of May, I would like to be able to see the total sales for the same period for the 1st-28th of January. 

 

Greatly appreciate it if someone can help me with this. Thank you in advance. 

 

  • 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

20 Replies

  • 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!

  • 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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello

     

    Try

    calculate((yourmeasure), sameperiodlastyear(date[Date]=

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Does not seem to work....

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Do you have a date table ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes I do have a date table Anonymous 

  • HenriqueReis's avatar
    HenriqueReis
    Icon for Resolver I rankResolver I
    4 years ago

    Hi, how are you?

     

    You should create a measure with this formula to see the before value you want:

     

    Then, put the columns on a table visual and you gonna have this:

     

    Notice I created the slicer visual too.

    So, filter the visual according your preference and remove the column "VALUE" if you want:

     

     

    I hope it helps you.

     

    REGARDS!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately it has not. But thanks anyways.

    • Anonymous's avatar
      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity 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
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous 

      My mistake. I mean DATESINPERIOD and to change the period to -2 month

      DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, -2, MONTH )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous 

    Has your problem been solved ? I see that Whitewater100 tamerj1  have given a reply, is this helpful to your question? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.