cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Matrix Visual Dynamic Calculation between values

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: 

 

CategoryAmountReport Date
Tech1235/25/2020
Facilities1235/25/2020
Marketing1235/25/2020
Tech2345/26/2020
Facilities2345/26/2020
Marketing2345/26/2020
Tech3455/27/2020
Facilities3455/27/2020
Marketing3455/27/2020

 

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

CategoryCurrent AmountDelta From 2 days ago
Tech345222
Facilities345222
Marketing345

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! 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

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:

Number_days.gif


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

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

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Super User III
Super User III

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:

Number_days.gif


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

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!

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

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors