Comparing values from last period when date is always different using slicers before and after

[mikelee1701]

Hello,

I have 3 tables:

1) date table

2) holiday table that is linked to the date table

3) the value table.

The value (SRT) table looks like this.

 The table with dates going back 3 years along with it's values.

The objective is to compare any year to any prior year during the holidays, more specifically because Thanksgiving lands on a different day, I have slicer with the from the Holiday table, so that I can choose Thankgiving for example, then it'll automatically compare the values Thanksgiving from say 2019 to 2020.  Furthermore, I can adjust the before and after timespan so if I choose one day before Thanksgiving, and 1 day after thanksgiving, i'd get a sum of the 3 days so I can compare 2019 to 2020 for the 3 days. 

My Problem is that no matter what I do, my values are all summed together on 11/28/2019, 11/26/2020.  I want that value for the prior day, day of Thanksgiving, and day after separately on each date line(for each year)

Someone helped me out with this:

Holiday Between=

var HolStart1 = min('HOLIDAYS-1'[Date]) - 'Days Before'[Days Before Value]

var HolEnd1 = MIN('HOLIDAYS-1'[Date]) + 'Days After'[Days After Value]

return CALCULATE(sum('SRT'[Value]),

AND(SRT[Date]>= HolStart1,

SRT[Date]<= HolEnd1)

)

Heres what It looks like:

Thank you all.

MIke

[Anonymous]

Hi @mikelee1701 ,

Sample data without sensitive information and expected output would help tremendously.
Please see this post regarding: How-to-provide-sample-data-in-the-Power-BI-Forum 

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @mikelee1701 ,

This has to do with context, since you are making the calculation of the values between the two dates you will never get the values for each date.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[mikelee1701]

Hi @MFelix , 

Here is a sample pbix I modeled after.

If you notice you can adjust the days before and after Thanksgiving, the value changes, which is fine, but now I want to show the dates(and their values) before and after on separate lines.

pbix sample slicer 

Thank you!

Mike

[MFelix · Accepted solution]

Hi @mikelee1701 ,

Looking at your model there is no need to redo your measure. This is a questions about how you setup the table.

I have made the following changes:

• Hide the Date on the Sales Table
• Show the Date on the Date Table
• Used the Date from the Date table instead of the Holidays table

Result below (table on the rigth my table, on the left your table:

WeTransfer Link.

[mikelee1701]

Hi @MFelix ,

Thank you.  

Let's continue with this sample pbix file.  My actual Pbix has sensive data, but it is mostly modeled after this one.

What I'm also trying to do is compare the the sales of Thanksgiving Year to year.

So if I cntrl click on the Years and choose more then one year, for example 2011 and 2012, I want to see the sales difference between 2011 and 2012.  Furthermore, if I want to change the slicers for a few days before Thanksgiving, and a few days after Thanksgiving, I'd like to see the days and sales differences from 1st year to 2nd year, day by day.

Currently, if I click more then one year, your chart only shows the first year, and on top of that 11-25-2011 is not Thanksgiving (but at this point i'm not too concerned about this yet).  I'm more interested in seeing the days before and after Thanksgiving with each value per days before/after.

Thank you,

Mike

[mikelee1701]

Hi @MFelix ,

I think I solved my own question.  I added another measure with the following:

Holiday Between max =

var HolStart = max(Holidays[Date]) - 'Days Before'[Days Before Value]

var HolEnd = max(Holidays[Date]) + 'Days After'[Days After Value]

RETURN

CALCULATE(sum(Sales[SalesAmount]),

AND(Sales[DateKey]>=HolStart,

Sales[DateKey]<=HolEnd)

)

But now I'm trying to graph it so it shows a trend comparison and the graphs are off.... but that's for another post 🙂

Thank you!

MIke