Forum Discussion
Compare MTD with previous period
Hi, I want to compare actual sales (running value) with sales in the same month last year. This should be shown in a Table and grouped by sales representative.
I have a fact table with the sales value per order and a date. Since the date in the fact table is not continuous I created a date-table and set up a relationship I created several measures:
Sales MTD = CALCULATE([Total Sales]; DATESMTD(Time[Date]))
Sales YTD = CALCULATE([Total Sales]; DATESYTD(Time[Date]))
Sales MTD last year = CALCULATE([Sales MTD]; SAMEPERIODLASTYEAR(Time[Date]))
Sales YTD last year = CALCULATE([Sales YTD]; SAMEPERIODLASTYEAR(Time[Date]))
I added a filter for month and year to the visualisation table, without a filter no data is shown. This works OK for the actual period, but not for the last period. Today the table should show all sales from 1. Of March to 24. Of march, which is OK, but for the last period it shows the sales from 1. To 31. Of March last year. – but I want to compare the exact period of time.
What am I missing?
I finally solved it: I filter my measure with the datesbetween function. I created a measure for the beginning of the month last year (eomonth function) and another for the actual date (=EDATE(TODAY);-12)
This way I have always the actual month compared to the month last year, even if the month is not yet finished.
19 Replies
TheOckieMofoResolver II
I'm thinking one of two methods should work for you.
1. On the Sales MTD measure, you shouldn't need the DATESMTD filter if you're added the month from your dates table to the filter of this table. Choosing a month from the dates table will automatically sum that whole month due to the relationship you've set up. I'm thinking that DATESMTD looks over the whole month and therefore when you pass that measure through the prior year measure, the prior year measure is looking over the whole month. Try making your Sales MTD measure just = Sum[total sales]. See if that works.
2. If that doesn't work, you might want to use the DATESBETWEEN function using two dynamic dates that will give you the prior year MTD range that you need. For example, you could use EOMONTH(TODAY(),-13)+1 to be the first day of the same month from last year. For the max date, I'd have to understand more about your data to see the best way to get that, but you should be able to figure something out.
Good luck and have fun!
- MattAllington
Community Champion
Your formulas are correct, but your filters are not strictly correct. Plus I am guessing your dates table has date over run - the dates go into the future even though the future hasn't arrived yet. A MTD measure relies on you selecting the last date (not month) that you need. So if you select March 2016 and your calendar table has future dates, then the last date in the current filter context will be 31 March. This year will give you up to 24th because the future sales haven't arrived, but the SAMEPERIODLASTYEAR will take the last date in the filter context (31st March) and the ten time shift back one year.
The easiest way to solve this is to only load your calendar table with dates up until the last sale date. You can use Power Query to solve is - check out my blog post on the topic here http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
if you make this change, you can select 2016 for the period, and it will work for YTD and MTD.
Hi Matt,
thank you very much for your reply. I tried your suggested solution. As I am working with Power BI Desktop I created a date table using the calendar function, which gave me a table starting with my first sales date and ending with the last one. I changed my YTD and MTD measures using the new table and also changed the relationship between the fact-table and the date table. Unfortunately, it did not change the result. The YTD and MTD for the previous period are still calculated until the End of the month. Any further suggestions?
- MattAllington
I have not experienced this before, but it is possible power bi behaves slightly different to excel. I think the simplest answer is to apply the filter on the current date, not the current month. In effect when you put a filter for March 2016 and it is only 24th March, you are actually saying "give me everything up until 31st March this year, and the same last year". The only reason you don't get up until 31st March this year is it doesn't yet exist.
You could try this
=Calculate([sales ytd],datesadd(Time[date],-1,Year))
