Subtracting fields from 2 table visuals

[nrenaud]

Hi there,

I am trying to create a table or a visual that will show me the difference between 2 different ratios - but the ratios are in different time periods.

For instance, this is what I'm working with currently:

As you can see, I have the same fields, but 2 different time filters applied to the 2 different visuals. I'd like to find a way to be able to show the difference between the 2 fields; does not matter if it's in the same visual or in separate visuals.

Is there a way to show 2 different time lines on the same visual? Or a way to subtract fields that are in multiple visuals?

Thanks in advance! 🙂

[danno · Accepted solution]

Visuals are purely a way to output the results of a DAX calculation into a visual form, you can't access the values from the visuals.  If you want to compare values in a Power BI Model, you need to create measures for the values you are trying to compare.  The measures act independently from the visuals, and can therefore be compared against each other.   If you have filtered the visuals to get the results for different time periods then you need to do the same for the measures.  You will need some time intelligence and/or filters on your measures to move the calculation to act against different date ranges.  If you have a measure for RTTPCa Ratio, then modify it using CALCULATE to change the filter context in a new measure.  Assuming you have a calendar table, the measure would look like:

RTTPCa Ratio Last 90 days = RETURN CALCULATE([RTTPCa Ratio], ALL('Calendar'), DATESBETWEN(Calendar[Date], TODAY()-90, TODAY())

The logic you would need for the last 6 months will depend a bit on how you determine the period and where it starts.  see post here: https://community.powerbi.com/t5/Desktop/DAX-formula-to-return-data-for-last-6-months/td-p/50455

let me know if this helps

[danno · Accepted solution]

Visuals are purely a way to output the results of a DAX calculation into a visual form, you can't access the values from the visuals.  If you want to compare values in a Power BI Model, you need to create measures for the values you are trying to compare.  The measures act independently from the visuals, and can therefore be compared against each other.   If you have filtered the visuals to get the results for different time periods then you need to do the same for the measures.  You will need some time intelligence and/or filters on your measures to move the calculation to act against different date ranges.  If you have a measure for RTTPCa Ratio, then modify it using CALCULATE to change the filter context in a new measure.  Assuming you have a calendar table, the measure would look like:

RTTPCa Ratio Last 90 days = RETURN CALCULATE([RTTPCa Ratio], ALL('Calendar'), DATESBETWEN(Calendar[Date], TODAY()-90, TODAY())

The logic you would need for the last 6 months will depend a bit on how you determine the period and where it starts.  see post here: https://community.powerbi.com/t5/Desktop/DAX-formula-to-return-data-for-last-6-months/td-p/50455

let me know if this helps

[nrenaud]

Thanks for this!

However, in creating the measures, what if I already have column that can identify the time periods I am trying to compare against? I can use these to filter down 1 visual by the time period I want, but I'm not sure if I could apply them to a measure so I could subtract the 2 values by different time periods. The time periods can be filtered by current month, past 2 quarters, and trailing 12 months.

For instance, I know this isn't the correct way to do this, but this is what I am thinking I'd like to be able to do:

RTTPCa Ratio Difference = (RTTPCa Ratio.Past2Quarters - RTTPCa Ratio.Current Month)

Hopefully that makes sense!

Thanks in advance!

[danno · Accepted solution]

Sure you can, you just need to use the CALCULATE function to apply a filter to the measure to use a different set of rows.  

What you are doing in the visual is applying a filter to the set of data to get a result.  When you use the Calculate function in a measure, you are applying a filter to the measure directly.  

If you create 2 separate measures, one for RTTPCa Ratio Past2Quarters and one for RTTPCa Ratio Current Month then you can subtract one from the other to get the difference.