topic Re: MTD FOR PREVIOUS PERIOD in DAX Commands and Tips

MTD FOR PREVIOUS PERIOD

Anonymous — Sat, 28 May 2022 21:40:11 GMT

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.

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Sat, 28 May 2022 21:51:38 GMT

Hello

Try

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

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Sat, 28 May 2022 22:19:56 GMT

Does not seem to work....

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Sat, 28 May 2022 22:22:46 GMT

Do you have a date table ?

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Sun, 29 May 2022 06:38:50 GMT

Yes I do have a date table Anonymous

Re: MTD FOR PREVIOUS PERIOD

HenriqueReis — Sun, 29 May 2022 10:15:30 GMT

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!

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Sun, 29 May 2022 11:51:59 GMT

Unfortunately it has not. But thanks anyways.

Re: MTD FOR PREVIOUS PERIOD

tamerj1 — Sun, 29 May 2022 11:57:15 GMT

Hi Anonymous

you can tey something like this https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-Cumulative-Total-Over-last-3-Year-based-on-Selected-Year/m-p/2544240#M71758

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.

Re: MTD FOR PREVIOUS PERIOD

Whitewater100 — Sun, 29 May 2022 12:03:39 GMT

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!

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Mon, 30 May 2022 08:32:52 GMT

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

Re: MTD FOR PREVIOUS PERIOD

tamerj1 — Mon, 30 May 2022 08:36:55 GMT

Anonymous

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

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

Re: MTD FOR PREVIOUS PERIOD

tamerj1 — Mon, 30 May 2022 08:59:28 GMT

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

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Mon, 30 May 2022 09:14:53 GMT

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

Re: MTD FOR PREVIOUS PERIOD

tamerj1 — Mon, 30 May 2022 09:21:44 GMT

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

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Mon, 30 May 2022 09:36:27 GMT

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

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Mon, 30 May 2022 09:45:06 GMT

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

Re: MTD FOR PREVIOUS PERIOD

tamerj1 — Mon, 30 May 2022 11:13:04 GMT

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

Re: MTD FOR PREVIOUS PERIOD

Anonymous — Mon, 30 May 2022 11:25:27 GMT

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

Re: MTD FOR PREVIOUS PERIOD

tamerj1 — Mon, 30 May 2022 11:29:20 GMT

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.

Re: MTD FOR PREVIOUS PERIOD

Whitewater100 — Mon, 30 May 2022 12:19:52 GMT

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.