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

[mikelee1701]

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

[v-jayw-msft]

Hi @mikelee1701 ,

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

[mikelee1701]

@v-jayw-msft ,

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

[v-jayw-msft]

Hi @mikelee1701 ,

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

[mikelee1701]

Hi @v-jayw-msft ,

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

[v-jayw-msft · Accepted solution]

Hi @mikelee1701 ,

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

[mikelee1701]

@v-jayw-msft ,

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

[v-jayw-msft]

Hi @mikelee1701 ,

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

[AllisonKennedy]

@mikelee1701 

See if this post helps: https://excelwithallison.blogspot.com/2021/09/power-bi-forecasting-with-irregular.html

Should be similar to what you're trying to do.

[mikelee1701]

@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...