Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi Anonymous ,

     

    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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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