Matrix Visual Dynamic Calculation between values

[2487jbml]

Hey all, 

I've tried looking and thinking of any way to do this and am stuck.  I have one data set that is structured similar to below: 

Category	Amount	Report Date
Tech	123	5/25/2020
Facilities	123	5/25/2020
Marketing	123	5/25/2020
Tech	234	5/26/2020
Facilities	234	5/26/2020
Marketing	234	5/26/2020
Tech	345	5/27/2020
Facilities	345	5/27/2020
Marketing	345	5/27/2020

I have a matrix visual that displays the current amount and then the delta from two days prior.  Like this:

Category	Current Amount	Delta From 2 days ago
Tech	345	222
Facilities	345	222
Marketing	345	222

In the actual data set, there is data from the previous 30, or more, business days.  My users would like to be able to select which day the delta is calculated from.  The only thing I can think of at the moment is having to create two different matrix views, but I don't think I could show the delta then.  I would prefer just to present the difference in amount between the current day and the date selected in a slider.  I hope I've articulated this well enough.  Right now Current Amount is calculated like this: Current Amount = IF('table'[Report Date]) = TODAY(), Amount).  The delta is calculated using a Previous Amount column calculated like this: Previous Amount = IF('table'[Report Date]) = TODAY() - 2, Amount).  Then I calculate the delta Current Amount - Previous Amount.  There are multiple items that make up the amounts in each of these categories that I need to be able to track movement by, which is why I went this route.  I'm open, and hope, to learn a more efficient way.  

Thanks in advance! 

[MFelix · Accepted solution]

Hi @2487jbml ,

Don't know if you want to have more than one day selected but my calculations is based on a single selection. Also belive the best way is to have a calendar table but this solution is made only with a single table.

• Make an what if parameter to make your days selections
• Make the following measures:

Current = IF(DISTINCTCOUNT(FactTable[Report Date]) >1; BLANK();SUM(FactTable[Amount]))

X Days ago = [Current] - CALCULATE([Current]; FILTER(ALL(FactTable[Report Date]);FactTable[Report Date] = SELECTEDVALUE(FactTable[Report Date]) - [Number of days Value] ))

Now just setup the values has you need:

[MFelix · Accepted solution]

Hi @2487jbml ,

Don't know if you want to have more than one day selected but my calculations is based on a single selection. Also belive the best way is to have a calendar table but this solution is made only with a single table.

• Make an what if parameter to make your days selections
• Make the following measures:

Current = IF(DISTINCTCOUNT(FactTable[Report Date]) >1; BLANK();SUM(FactTable[Amount]))

X Days ago = [Current] - CALCULATE([Current]; FILTER(ALL(FactTable[Report Date]);FactTable[Report Date] = SELECTEDVALUE(FactTable[Report Date]) - [Number of days Value] ))

Now just setup the values has you need:

[2487jbml]

Hey Miguel, 

Your answer is a lot closer than what I had been able to get.  What I'm trying to determine is what the amount value was from X days ago.  Should I just be able to modify your answer below a little and get the value I'm after?  

I really appreciate your insight! 

Thanks!

[MFelix · Accepted solution]

Hi @2487jbml ,

The value I'm picking up is the value from X days ago, however since you presented the difference this is what I'm presenting.

This is however a dinamic approach were you can select the number of days and the date that you want to compare.

Can you explain a little bit better wath you mean by: 

"What I'm trying to determine is what the amount value was from X days ago."

[2487jbml]

Hey @MFelix , 

You are spot on friend, I was reading it differently in my non-coffee brain this morning.  Thanks so much for your help, you are awesome!  I have accepted your response as the solution.  

Thanks, 

Lennie