Forum Discussion
Compare MTD with previous period
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.
Community ChampionEverything 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.
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.