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

I shall try this but is there a way that I will be able to compate for example 1st to the 24th of May 2022 to any previous month, within the same period?

 

For example January or February 2022 1-24th, without explicitly changing the DAX? tamerj1 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

This method compares with the same period exactly as you wish. The only thing is that you can see the selected month and the previous N months. 
The original sample file is based on years. Whenever a Year and a month are selected the visual will show YTD value of the selected (Year-Month) as well as the YTD of the 3 previous years (up to the selected month).

With the modifications i shared with you, you should be able to select a date and the visual will show the MTD of the selected month and the MTD of the previous month up the selected "Day". For example if you select March 21 then you see MTD values for both March (up to March 21) and for February (Up to Feb 21).

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I left a comment previously. Perhaps it has been overlooked. But what is 

    'PreviousDate'[Date]

     Is it a separate table that I need to create? I am attaching my data model for your reference. Been stuck on this part tamerj1 

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 

    Sorry just noticed that.

    Previous Date = 'Date'

     

    Then create an inactive relationship between 'Date'[Date] (many side) and  'Previous Date'[Date] (one side) this should be one-way relationship.