Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ganenthra94
Helper I
Helper I

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. 

 

1 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

View solution in original post

20 REPLIES 20
v-yetao1-msft
Community Support
Community Support

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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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
Super User
Super User

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. 

@ganenthra94 

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

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

What do you mean change the period of PARALLELPERIOD to 1 month? Could not find it in the link you shared. Thanks once again.

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

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

Will try but would this enable me to compare with any other month? @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. 

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 

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

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 

 

Capture.PNG

@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
Resolver I
Resolver I

Hi, how are you?

 

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

HenriqueReis_0-1653819263272.png

 

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

HenriqueReis_1-1653819263488.png

 

Notice I created the slicer visual too.

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

HenriqueReis_2-1653819263482.png

 

 

I hope it helps you.

 

REGARDS!

Unfortunately it has not. But thanks anyways.

Anonymous
Not applicable

Do you have a date table ?

Yes I do have a date table @Anonymous 

ganenthra94
Helper I
Helper I

Does not seem to work....

Anonymous
Not applicable

Hello

 

Try

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors