cancel
Showing results for
Did you mean:
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:

 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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
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:

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

Super User III

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

4 REPLIES 4
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:

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

Frequent Visitor

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!

Super User III

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

Frequent Visitor

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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