cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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 20
Community Support

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.

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'

PMTD = CALCULATE([amount], DATEADD(FILTER(DATESMTD(Dates[Date]), DATES[Date]<TODAY()), -1,MONTH))
Prev MTD = CALCULATE([Amount],

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

Super User

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.

Super User

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

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

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

Super User

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```
Helper I

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

Helper I

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

Super User

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

Helper I

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

Super User

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

Helper I

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

Super User

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.

Resolver I

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!

Helper I

Unfortunately it has not. But thanks anyways.

Anonymous
Not applicable

Do you have a date table ?

Helper I

Yes I do have a date table @Anonymous

Helper I

Does not seem to work....

Anonymous
Not applicable

Hello

Try

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors