Set Analysis

[Zaga_69]

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

[Anil_Babu_Samineni]

If there is no concern from date field. You can try
Sum({1 <DateKey={">=$(=Date(YearStart(Max(DateKey)), 'YYYYMMDD')<=$(=Date(Today(),'YYYYMMDD'))"}>} [Product_Sales])

[Zaga_69]

Thanks, but unfortunately it did not work 😕

[Anil_Babu_Samineni]

Can you please share sample data set or application. Can you check this in text object and tell us what it returns
Date(YearStart(Max(DateKey)), 'YYYYMMDD')

[sunny_talwar]

---

@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?

[Zaga_69]

Hi

This formula

Date(YearStart(Max(DateKey)), 'YYYYMMDD')

brings the following result:

20180101

[Anil_Babu_Samineni]

Try this, make sure you are not looking the result after selection because you are ignoring all selections here. Check validation of expression as well

Sum({1 <DateKey={">=$(=Date(YearStart(Max(DateKey)), 'YYYYMMDD')<=$(=Date(Max(DateKey),'YYYYMMDD'))"}>} [Product_Sales])

[Zaga_69]

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

[sunny_talwar]

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?

[Zaga_69]

Hi

It brings the right date: