Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing different dates same time period using slicers to show trending previous vs current period

Hi,   I have a sample pbix here that I am comparing holiday season for example Thanksgiving(TG) from 2011 vs 2012 with slicers for days before and after. I want to get my graphs to show Trending c...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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
    ))
    RETURN
    IF(SELECTEDVALUE('Calendar'[DateKey])>=HolStart-Hol_diff&&SELECTEDVALUE('Calendar'[DateKey])<=HolEnd-Hol_diff,_max)
     
     
    Best Regards,
    Jay
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

Modify the formula as below:

 

Holiday Between max = 
var HolStart = max(Holidays[Date]) - 'Days Before'[Days Before Value]
var HolEnd = max(Holidays[Date]) + 'Days After'[Days After Value]
var _max = CALCULATE(sum(Sales[SalesAmount]),
   FILTER(ALL('Sales'),
       Sales[DateKey]>=HolStart&&sales[DateKey]<=HolEnd&&Sales[DateKey]=SELECTEDVALUE('Calendar'[DateKey])+365
))
RETURN 
IF(SELECTEDVALUE('Calendar'[DateKey])>=HolStart-365&&SELECTEDVALUE('Calendar'[DateKey])<=HolEnd-365,_max)

 

Result would be shown as below:

 

Best Regards,

Jay

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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
        ))
        RETURN
        IF(SELECTEDVALUE('Calendar'[DateKey])>=HolStart-Hol_diff&&SELECTEDVALUE('Calendar'[DateKey])<=HolEnd-Hol_diff,_max)
         
         
        Best Regards,
        Jay