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 ,

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      You are welcome.

      The formula you wrote is already quite remarkable. What I did was just a little modification based on your formula.

       

      Best Regards,

      Jay