As a side question, let's say current date = 28 Feb 2017
What is your PMTD and PYMTD ?
(keep in mind 2016 was a leap year, 29.02.2016 was a thing)
Because the answer to your questions first depend on the business rules behind these things.
Thanks Gianni for your question.
we had same question in our mind still we need to go back to customer on that part.
we are thinking
if our current_date is 28 Feb 2017 PMTD would be 1Jan2017 to 28 Jan2017
and PYMTD would be 01FEB2016 to 28FEB2016
So 29 Feb 2016 will never exist in your results as well as 29-31 Jan 2017.
Define the full logic with the business first, covering all these weird cases first. Clearly challenging them on their decision, the technical solution for that logic could change based on the answers ...
we would be having issues with the leap year. if we exclude that scenario how can we achieve it.
If you exclude leap years, then normal time series functions will work.
For MTD i have used todate and it is working fine. but for previous Month MTD and previous year same month MTD is not working as expected.
Date Measure MTD PMTD PYMTD
1-Jan-2019 10 10
2-Jan-2019 20 30
1-Feb-2019 40 40 10 value which was there on 1-Feb-2018
2-Feb-2019 50 90 30 value which was there on 2-Feb-2018
The question is - what's your *exact* todate formula?
TODATE(Measure,month level from hierarchy)
Your EXACT formula.
Because if you put the wrong value for the level and the level has wrong keys then it won't work. An MTD measure knows when its month starts. It can't be wrong. only your formula or dimension can be wrong.
Thanks Christian. But I am sure formula is correct. Because rolling 3 months, rolling 1 month and month ago is working perfectly.I will keep you posted on monday along with screenshots which will include column formula and hierarchy.
But I am sure formula is correct.
...but for previous Month MTD and previous year same month MTD is not working as expected.
You are sure it is correct and you say it isn't working "as expected", but still you don't want to post the exact formulas you are using for the 3 cases...
I wasnt having access to my system as I have already left for the day yesterday. So I have clearly mentioned in my last comment that I will paste formula and screenshots on monday.
I have clearly mentioned that MTD formula is correct and is working as expected. Previous month Mtd and previous year same month mtd is not working. I have asked for suggestions on how can we achieve it.