Calculating Difference between Values from two different visuals.

[lamlam24]

I have inserted a link to the pbix file I need help with. 

There are 3 matrix visuals in the report. Below is what you should see when you open the report.

1. Visual 1 is Partner Visual

2. Visual 2 is Clinician Visual

3. Visual 3 is called DELTA (The value in Visual 3 should be Value in Clinician Visual - Value in Partner Visual).

When I select a date in the past, the DELTA visual (Which is the difference between the values in the Clinician Visual - Partner Visual), the results are correct.

However, when I select a date in the future, the DELTA visual just gives a -ve value of all the Values in the partner visual.

I need help creating a DAX that subtracts the value in the partner visual from the clinician visual.

I created a DAX but it seems to be giving inaccurate values especially when the date is in the future.

You can use the date filter in the Power Bi file to filter the reports to understand my explanation.

Currently, this is the DAX measure I created to get the Value in the 3rd visual

NEWMEASURE_DELTA = CALCULATE (

        SWITCH (

            SELECTEDVALUE ( 'f_PCIC_volumebystateview'[CallTime (formatted)] ),

            "12 AM", [Scalar_Hr_00],

            "1 AM", [Scalar_Hr_01],

            "2 AM", [Scalar_Hr_02],

            "3 AM", [Scalar_Hr_03],

            "4 AM", [Scalar_Hr_04],

            "5 AM", [Scalar_Hr_05],

            "6 AM", [Scalar_Hr_06],

            "7 AM", [Scalar_Hr_07],

            "8 AM", [Scalar_Hr_08],

            "9 AM", [Scalar_Hr_09],

            "10 AM", [Scalar_Hr_10],

            "11 AM", [Scalar_Hr_11],

            "12 PM", [Scalar_Hr_12],

            "1 PM", [Scalar_Hr_13],

            "2 PM", [Scalar_Hr_14],

            "3 PM", [Scalar_Hr_15],

            "4 PM", [Scalar_Hr_16],

            "5 PM", [Scalar_Hr_17],

            "6 PM", [Scalar_Hr_18],

            "7 PM", [Scalar_Hr_19],

            "8 PM", [Scalar_Hr_20],

            "9 PM", [Scalar_Hr_21],

            "10 PM", [Scalar_Hr_22],

            "11 PM", [Scalar_Hr_23]

        ) - [Average Demand Rate for Last 6 Weeks Selected_Day_Of_Week],

        '*** ODC DIM TABLE'[OD_Team_New]

            = SELECTEDVALUE ( '*** ODC DIM TABLE'[OD_Team_New] )

    )

Here is the link to the report:

https://drive.google.com/file/d/1EmujrPca2L0qevVZ-PCRsoHRzUtqZCAW/view?usp=drive_web

@Greg_Deckler , @amitchandak , @Ashish_Mathur @dufoq3 @lbendlin 

[johnbasha33]

@lamlam24 

Thanks for providing the details and the Power BI file. It seems like the issue you're facing might be related to how your DAX measure handles future dates. Let's try to improve the measure to accurately calculate the difference between the values in the Clinician Visual and the Partner Visual.

Here's a revised version of your DAX measure:

```DAX
NEWMEASURE_DELTA =
VAR ClinicianValue =
SWITCH (
SELECTEDVALUE ( 'f_PCIC_volumebystateview'[CallTime (formatted)] ),
"12 AM", [Scalar_Hr_00],
"1 AM", [Scalar_Hr_01],
"2 AM", [Scalar_Hr_02],
"3 AM", [Scalar_Hr_03],
"4 AM", [Scalar_Hr_04],
"5 AM", [Scalar_Hr_05],
"6 AM", [Scalar_Hr_06],
"7 AM", [Scalar_Hr_07],
"8 AM", [Scalar_Hr_08],
"9 AM", [Scalar_Hr_09],
"10 AM", [Scalar_Hr_10],
"11 AM", [Scalar_Hr_11],
"12 PM", [Scalar_Hr_12],
"1 PM", [Scalar_Hr_13],
"2 PM", [Scalar_Hr_14],
"3 PM", [Scalar_Hr_15],
"4 PM", [Scalar_Hr_16],
"5 PM", [Scalar_Hr_17],
"6 PM", [Scalar_Hr_18],
"7 PM", [Scalar_Hr_19],
"8 PM", [Scalar_Hr_20],
"9 PM", [Scalar_Hr_21],
"10 PM", [Scalar_Hr_22],
"11 PM", [Scalar_Hr_23]
)
VAR PartnerValue =
CALCULATE (
[Average Demand Rate for Last 6 Weeks Selected_Day_Of_Week],
'*** ODC DIM TABLE'[OD_Team_New] = SELECTEDVALUE ( '*** ODC DIM TABLE'[OD_Team_New] )
)
RETURN
ClinicianValue - PartnerValue
```

This measure first calculates the value in the Clinician Visual based on the selected time slot. Then, it calculates the value in the Partner Visual using the [Average Demand Rate for Last 6 Weeks Selected_Day_Of_Week] measure. Finally, it returns the difference between these two values.

Make sure to replace the placeholder measures and table names with the actual ones used in your Power BI report. Test this measure with past and future dates to see if it resolves the issue you're facing. If you encounter any further issues or need more assistance, feel free to ask!

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

[lamlam24]

Thank you for looking at this. But the new DAX you provided still does the same thing. if you use the date filter to filter for any date in the future it still doesn't do the subtraction. You can check with the file i sent.

[lbendlin]

Unpivot your Demand_Supply_Table to get rid of all the hourly measures.