Forum Discussion
Subtracting fields from 2 table visuals
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
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.
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!
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.