Subtracting two columns of data and need to break down columns with filters

[ghobbs]

Hi,

Thank you in advance for the support.

I'm trying to build a table, or matrix, whichever works, that subtracts the values in each row.  I've tried to do it with a table and with a matrix and get stuck in both ways.

The closest that I got was to create a table, with multiple measures, and then subtract them.  The equation works, but when I want to break the data down by part, it still gives me the grand total.  I know I'm doing something wrong but don't know how to solve it.

Thank you

Table

Trying to take the sum of dollars with one SNAPSHOT date and subtract them from a sum of dollars in a second SNAPSHOT date and review by part id.

Measure - Measure2 = Measure3 

When I do this again, it keep the total and doesn't give the total per part.  The subtraction works but I can't see the part dollar values and their differences.

-----

Measure =

CALCULATE(

   SUM(Query2[USD_EXT_COST]),

    FILTER(

        ALLSELECTED(Query2),

        Query2[SNAPSHOT_DATE] = MAX(Query2[SNAPSHOT_DATE]))

)

Measure 2 = VAR z =

CALCULATE(

    SUM(Query2[USD_EXT_COST]),

    FILTER(

        ALLSELECTED(Query2),

        Query2[SNAPSHOT_DATE] < MAX(Query2[SNAPSHOT_DATE]))

    )

RETURN

z

Measure 3 = VAR d =

CALCULATE(

[Measure]-[Measure 2])

RETURN

d

[Kedar_Pande]

@ghobbs 

Measure = CALCULATE(
SUM(Query2[USD_EXT_COST]),
FILTER(
ALLSELECTED(Query2),
Query2[SNAPSHOT_DATE] = MAX(Query2[SNAPSHOT_DATE])
)
)

Measure 2 = 
VAR z = 
CALCULATE(
SUM(Query2[USD_EXT_COST]),
FILTER(
ALLSELECTED(Query2),
Query2[SNAPSHOT_DATE] < MAX(Query2[SNAPSHOT_DATE])
)
)
RETURN z

Measure 3 = 
VAR d = [Measure] - [Measure 2]
RETURN d

When using your matrix or table, ensure you add PART_NBR to the rows to break down the values by part.
If you still see grand totals, check the visual settings. You may want to disable the grand total for the rows if it’s included by default in the matrix visual's settings.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[sevenhills]

Check this video

Compare previous date with sales - Unplugged #52

https://www.youtube.com/watch?v=sETL5-enLEk

[DataNinja777]

Hi @ghobbs ,

The issue occurs because ALLSELECTED modifies the filter context. Specifically, when you use ALLSELECTED(Query2), all filters applied to Query2—including the PartID filter—are ignored in that calculation. This behavior can lead to the unwanted grand total or aggregated results you are seeing.

To better understand and solve this issue, let’s dive into how ALLSELECTED and FILTER are interacting in your DAX and how to fix it so that part-level filtering remains intact.

ALLSELECTED removes filters but preserves only those applied by slicers or visuals.

If PartID is included in the matrix or table’s rows, but you use ALLSELECTED(Query2), the filter on PartID is ignored, which is why the total sum is calculated instead of breaking it down by part.

Instead of using ALLSELECTED(Query2), you can keep the part-level filter context by adjusting the DAX formulas. Here’s how you can revise the measures to ensure that the calculation respects the part number filter:

Measure1 =
CALCULATE(
    SUM(Query2[USD_EXT_COST]),
    FILTER(
        Query2, 
        Query2[SNAPSHOT_DATE] = MAX(Query2[SNAPSHOT_DATE])
    )
)

Measure2 =
CALCULATE(
    SUM(Query2[USD_EXT_COST]),
    FILTER(
        Query2, 
        Query2[SNAPSHOT_DATE] = MAX(Query2[SNAPSHOT_DATE]) - 1
    )
)

Measure3 = 
[Measure1] - [Measure2]

By removing ALLSELECTED, the PartID filter from the table or matrix will now be honored in the calculations.

We filter based on the relevant snapshot dates without disrupting the current filter context for PartID.

[ghobbs]

Thank you!  So close!

Your code solved the SUM issue by row but presented another issue.  I notice in your code, that you changed the MAX < SNAPSHOT DATE code to SNAPSHOT DATE - 1.  This will give me the week I selected and the week before it.  Can I get it to the point where it will select the two dates that I select no matter how far apart that they are?

Sometimes we need to compare data that is 3,4+ weeks apart.

Thank you!

[DataNinja777]

Hi @ghobbs ,

Great observation! You're right—my initial solution assumes consecutive dates, but if your requirement is to compare any two selected dates, no matter how far apart, we need a more flexible approach.

Here’s a revised version of the DAX code to allow for two selected dates. The goal is to ensure that both selected dates are respected in the calculation.

Assuming you’re working with a date slicer or two separate slicers for the comparison dates, the following solution ensures the selected dates are respected.

Measure1 = 
CALCULATE(
    SUM(Query2[USD_EXT_COST]),
    Query2[SNAPSHOT_DATE] = SELECTEDVALUE('Calendar'[Date])
)

Cost for the Second Selected Date

Measure2 = 
CALCULATE(
    SUM(Query2[USD_EXT_COST]),
    Query2[SNAPSHOT_DATE] = SELECTEDVALUE('Calendar'[Comparison Date])
)

Difference Between the Two Dates

Measure3 = 
[Measure1] - [Measure2]

SELECTEDVALUE: This function grabs the value from a slicer or visual selection. If only one date is selected in each slicer, it will apply that specific filter.

Two Date Fields:

• The main Calendar table contains the primary date used in the slicer.
• A separate Comparison Date slicer allows users to pick another date for comparison.

This approach ensures that users can select any two dates—whether they are consecutive or weeks/months apart.

Best regards,

[ghobbs]

Thank you very much for laying this out.  I understand this in principal but I can't get this to work in the dashboard. 

At first I tried to create two different dates in a new calendar table but it didn't work.  When I created slicers and clicked the first date there was no option to choose a date in the slicer for the second date.  Guessing since they are both dates, and in the same table, that they would just override each other.

Then I created two different calendar tables and linked both date key fields to the SNAPSHOT date field in the original data table 'Query 2'.  This also didn't work.  I received data when selecting the first date in the dashboard but received no data when selecting the second date. 

What do you think the issue is?

Thank you