Forum Discussion
Comparing different dates same time period using slicers to show trending previous vs current period
Hi Anonymous ,
I see. So we need to add the date difference to the formula.
Holiday Between max =var HolStart = max(Holidays[Date]) - 'Days Before'[Days Before Value]var HolEnd = max(Holidays[Date]) + 'Days After'[Days After Value]var Hol_diff = datediff(MIN(Holidays[Date]),MAX(Holidays[Date]),DAY)var _max = CALCULATE(sum(Sales[SalesAmount]),FILTER(ALL('Sales'),Sales[DateKey]>=HolStart&&sales[DateKey]<=HolEnd&&Sales[DateKey]=SELECTEDVALUE('Calendar'[DateKey])+Hol_diff))RETURNIF(SELECTEDVALUE('Calendar'[DateKey])>=HolStart-Hol_diff&&SELECTEDVALUE('Calendar'[DateKey])<=HolEnd-Hol_diff,_max)Best Regards,Jay
Anonymous ,
Thank you! I changed it to 364 instead of 365, but I can't figure out why it does not work when I select different year comparisons like 2012 and 2013. It should still shift correctly no matter the year right?
TIA,
Mike
Hi Anonymous ,
It's working fine on my side.
As you can see in the screenshot, 2012 Thanksgiving is from 11/20 to 11/26 and 2013 Thanksgiving is from 11/26 to 12/2.
It may be because they only overlap one day, so it's not obvious in the line chart.
And if you want compare 2011 with 2013, you will need use year difference * 365, like:
var year_difference = year(max(date))-year(min(date))
+/- year_difference*365
Holiday Between max =
var HolStart = max(Holidays[Date]) - 'Days Before'[Days Before Value]
var HolEnd = max(Holidays[Date]) + 'Days After'[Days After Value]
var year_difference = YEAR(MAX(Holidays[Date]))-YEAR(min(Holidays[Date]))
var _max = CALCULATE(sum(Sales[SalesAmount]),
FILTER(ALL('Sales'),
Sales[DateKey]>=HolStart&&sales[DateKey]<=HolEnd&&Sales[DateKey]=SELECTEDVALUE('Calendar'[DateKey])+year_difference*365
))
RETURN
IF(SELECTEDVALUE('Calendar'[DateKey])>=HolStart-year_difference*365&&SELECTEDVALUE('Calendar'[DateKey])<=HolEnd-year_difference*365,_max)
Best Regards,
Jay
Hi Anonymous ,
Thank you.
In the screenshot you sent, the lines are offset from each other. That's what I'm trying to avoid.
I need a comparison day to day and so on (on top of each other) like this:
no matter which years or holidays selected.
TIA,
Mike
Hi Anonymous ,
I see. So we need to add the date difference to the formula.
Holiday Between max =var HolStart = max(Holidays[Date]) - 'Days Before'[Days Before Value]var HolEnd = max(Holidays[Date]) + 'Days After'[Days After Value]var Hol_diff = datediff(MIN(Holidays[Date]),MAX(Holidays[Date]),DAY)var _max = CALCULATE(sum(Sales[SalesAmount]),FILTER(ALL('Sales'),Sales[DateKey]>=HolStart&&sales[DateKey]<=HolEnd&&Sales[DateKey]=SELECTEDVALUE('Calendar'[DateKey])+Hol_diff))RETURNIF(SELECTEDVALUE('Calendar'[DateKey])>=HolStart-Hol_diff&&SELECTEDVALUE('Calendar'[DateKey])<=HolEnd-Hol_diff,_max)Best Regards,JayAnonymous ,
Thank you! that works!
Now final question, but this is easy.
Can you tell me a bit about the type of person (like you) who can just simply type all that out so seemingly so easy? I am not a developer at heart. I have taken programming classes in the past like assembly, Pascal, Visual Basic, maybe one ore two others, but they were not career paths, nor I really want to get deep into.
This is something that just happen to land on me, and for the most part once it gets longer then 3 to 5 lines, I start to have a hard time. I spend too much time thinking about it even when "the day is over".
For the most part, I find what I can from the internet (and here), and then use their code, piece together what I can, maybe tweek it to get it to work most of the time.
What advice do you have for someone like me. Is picking this stuff up easier as you do it more? Is it a good idea to reuse code from the internet? What should I read to approach your level? I've been using PBI for about 3 years now.
Thanks again!
Mike