Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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
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.
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
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!
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.
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
@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
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.
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!
Unfortunately it has not. But thanks anyways.
Do you have a date table ?
Yes I do have a date table @Anonymous
Does not seem to work....
Hello
Try
calculate((yourmeasure), sameperiodlastyear(date[Date]=
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
34 | |
25 | |
24 | |
23 |