MTD FOR PREVIOUS PERIOD

[ganenthra94]

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. 

[Anonymous]

Hi  @ganenthra94 

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.

[Whitewater100]

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.

[Whitewater100]

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!

[tamerj1]

Hi @ganenthra94 

you can tey something like this https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-Cumulative-Total-Over-last-3-Year-bas...

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. 

[tamerj1]

@ganenthra94 

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

DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, -2, MONTH )

[ganenthra94]

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 · Accepted solution]

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

[ganenthra94]

Also what is the Previousdate? Is it a separate table that I need to create? @tamerj1 

[ganenthra94]

Will try but would this enable me to compare with any other month? @tamerj1 

[tamerj1]

@ganenthra94 
It will enable you to compare the selected month with the previous (N) months. So if you set the months -2 you will see 2 months (the selected month and the month before). If youe set the months -3 you will see 3 months (the selected month and the two before) and so on. 

[ganenthra94]

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]

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).

[ganenthra94]

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]

@ganenthra94 

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.

[HenriqueReis]

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!

[ganenthra94]

Unfortunately it has not. But thanks anyways.

[Anonymous]

Do you have a date table ?

[ganenthra94]

Yes I do have a date table @Anonymous 

[ganenthra94]

Does not seem to work....

[Anonymous]

Hello

Try

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