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 comparing day to day (first day to second day and so on) based on the slicers before TG and after.

Right now my graph looks like this when I select the two years:

my chart looks like this:

That's because the values are on a different timeframe, but how can I move it so that it is comparing from day 1?

 

I need the trend lines to stack on top of each other instead of either ends of the chart.

 

How can I do that?

sample holiday slicers graph 

 

 

TIA,

Mike

 

 

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

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      AllisonKennedy ,

       

      thank you... but my little brain is having a hard time making the connection.

      I also google a lot of places and I look for 'hot' areas to clue in(before actually diving into the code) on to help me and I'm not making the connection...

  • 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