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

[Zaga_69]

back to my original question, I would like to replace red part of this formula:

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

by this one:

date(addyears(date(today(),'YYYYMMDD'),-1),'YYYYMMDD')

However when replacing it does not work....

[sunny_talwar]

So, that works, but you think that this doesn't?

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

Can you try the above expression in a chart where you have DateKey as a dimension and share a screenshot

[sunny_talwar]

Try this expression and I would like you to share a screenshot where I can see 20181222, 20181223, 20181224, etc in a chart where you use the expression with DateKey as dimension

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

[Zaga_69]

Hi

It works!

Thanks, I really appreciate your effort and help! 

[sunny_talwar]

So, all good then?