Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I’m using the following formula to calculate YTD and Previous YTD:
YTD
Sum({1 <DateKey={"$(='>=' & Date(YearStart(Max(DateKey)), 'YYYYMMDD') & '<=' & Date(Today(),'YYYYMMDD')"}>} [Product_Sales])
Previous YTD
Sum({1 <DateKey={"$(='>=' & Date(YearStart(Max(DateKey)), 'YYYYMMDD') & '<=' & Date(Today(),'YYYYMMDD')"}>} [Product_Sales])
The formulas above work, however it calculates the YTD and Previous YTD from a month perspective. I would like for example to calculate YTD from a day perspective (i.e. sales between 20180101 & 20181221)
I tried to replace the second part of the formula using:
date(today(),'YYYYMMDD')
however, it did not work.
YTD
Sum({1 <DateKey={"$(='>=' & Date(YearStart(Max(DateKey)), 'YYYYMMDD') & '<=' & Date(Today(),'YYYYMMDD')"}>} [Product_Sales])
Finally DateKey was created as follows:
date(Date#("0PSTNG_DATE",'YYYYMMDD'),'YYYYMMDD') AS DateKey,
Any idea will be much appreciated.
Best regards,
Edi
Thanks, but unfortunately it did not work 😕
@Zaga_69 wrote:The formulas above work, however it calculates the YTD and Previous YTD from a month perspective. I would like for example to calculate YTD from a day perspective (i.e. sales between 20180101 & 20181221)
Can you elaborate on this? What do you mean when you say that the YTD and Previous YTD from a month perspective? Can you explain this with an example?
Hi
This formula
Date(YearStart(Max(DateKey)), 'YYYYMMDD')
brings the following result:
20180101
Hi
Month perspective:
when using the formulas posted previously, they calculates for example YTD 2017 (i.e. Previous YTD) taking into account the whole December. I would like to be able to compare the same period, for example:
YTD (20180101 & 20181221)
Previous YTD (20170101 & 20171221)
Instead of that the formula calculates for the Previous Year:
Previous YTD (20170101 & 20171231)
And it is because of this part: Date(addyears(Max(DateKey),-1), 'YYYYMMDD') which brings 20171231...
I tried to replace it by date(addyears(date(today(),'YYYYMMDD'),-1),'YYYYMMDD'), however it did not work
So when you use this in a text box object...
=date(addyears(date(today(),'YYYYMMDD'),-1),'YYYYMMDD')
you don't see this?
Can you show us the screenshot of what you see when you use the above in a text box object?
Hi
It brings the right date: