Compare MTD with previous period

[leebm]

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?

[MattAllington]

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.

[leebm]

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

[leebm]

Hi

I tried this, but your measure also gives me the value for the whole month.

when I show the data in a diagram instead of a table, the values are right as long as the detail-level is day. As soon as I switch to month or year, the grand total contains the whole month as in the table.

[kcantor]

My current work around for this is to add a day of the year column to my date table. Then, I filter to day of the year being less than the current day of the year so that I am looking at data through yesterday as today has not happened yet. For example, today is the 89th day of the year. My current filter is Day Of Year is less than 89. Primative but effective.

Here is the link to the current day of year number. It will only work when one year is selected, however, so make sure you are filtered to the current year.

http://www.epochconverter.com/daynumbers

Of course, this method works on your original MTD and YTD calculations and not on the other that covers until yesterday by using datesbetween. It also allows the user to slice by week or month.

[leebm]

How does this help you in the previous periods? - I have no problem with the actual period, but with the previous ones. I also don´t understand how you make this filter dynamic, or do you change it every day?

[kcantor]

Everything works fine until we get to months that have only partial results due to being within that period.  Generally, my reports focus down to the week number in which case I have a field I added that shows the year and week together such as 2016-08 for the eight week of 2016. This allows me to use full weeks across multiple years within a report by adding the year-week to the axis or row instead of week number which would calculate week 8 for every year selected in my data and sum it up. If, however, the PTB want up to the current day, I have the option to filter the report by current year and day of the year using calculations built on total sales, LY total sales, and 2 year prior total sales up to the current day of the year. While this doesn't happen often, the request has been made in the past.

Then again, on second thought, if you are only interested in looking at data to the level of week over week within the given month, the year-week calculation may work for you.

As I said, day of the year is a workaround. Not a solution. I generally keep my date table down to the current day only which helps.

FYI: The PTB are the powers that be. They always ask for odd calculations or weird time frames. I think they just want to know if I can work that out.

[leebm · Accepted solution]

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.

[Anonymous]

Hi @leebm ,

Could you be able to show a pbxi file?

I am looking for similar solution, however does not get it yet.

Hope it is Ok to create a new post , and invite you for a answer. 😉

Best regards,

Yuqi

[Prateek03]

Hi @leebm i'm facing the same issue, can you please share the DAX of how you solved this.

Thanks

[vinaybangari]

Hi Leebm,

Can you please post your complete DAX expression for previous YTD, MTD & QTD periods

[vinaybangari]

This is Previous YTD works fine: Prev YTD = CALCULATE([Current YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

Prev MTD Period Balance = CALCULATE([Period Balance],DATEADD('Calendar'[Date],-1,MONTH))

[Sachet_716]

Hi Lee,

Help me with this please... 

Rgds, Cristina

[themistoklis]

@Sachet_716

@ako

@jbjs5820

This is the formula that you can use:

Turnover MTD (PY) = CALCULATE(SUM('Invoices to Clients'[TurnOver]),
        DATESBETWEEN('Calendar'[Date],                    
            FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)),   
            IF(MONTH(MIN('Calendar'[Date])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))) 
         )
     )

I have put the IF statement because i want for the months prior to current month to get all the dates

[jbjs5820]

Can you insert the total formulas?

Thanks and regards

JS

[ako]

Hi leebm!

I have the exact same problem. Could you please explain the solution more presis for me?  (I'm a slow learner 🙂

[jbjs5820]

Could you insert the total formula?

[Sachet_716]

Hi!  

Were you able to figure this out? Can you share the measure please? Been searching for this function and it's a show stopper for me, can't go ahead without completing this. thank you in advance! 🙂

[TheOckieMofo]

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!