Sum Difference between two date entries via matrix

[annie_liu]

Hello,

   I was trying to get the difference between the Grade Level between the latest week and the prior week within this matrix table.

so this one I want to see a measure that say Difference:  Take that latest $ subtract from the prior week pull date.

Sometimes the Pulled date is out of order so it would 10/16/2023 would be the 2nd column and  10/9/2023 would be the 3rd column, so the measure subtracts the latest data from the earlier data.

Rows ---->GL

Column -----> Pulled Date 

Values---->   Sum of Total: Budget 

[Anonymous]

Hi @annie_liu ,

You can create two measures as below to get it, please find the details in the attachment.

Sum of Budget = SUM('Table'[Budget]) 

Difference =
VAR _gl =
    SELECTEDVALUE ( 'Table'[GL] )
VAR _latestwk =
    CALCULATE (
        MAX ( 'Table'[Pulled Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[GL] = _gl && [Sum of Budget] > 0 )
    )
VAR _preweek =
    CALCULATE (
        MAX ( 'Table'[Pulled Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[GL] = _gl
                && [Sum of Budget] > 0
                && 'Table'[Pulled Date] < _latestwk
        )
    )
VAR _lwkbuddget =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[GL] = _gl
                && 'Table'[Pulled Date] = _latestwk
        ),
        [Sum of Budget]
    )
VAR _pwkbuddget =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[GL] = _gl
                && 'Table'[Pulled Date] = _preweek
        ),
        [Sum of Budget]
    )
RETURN
    _lwkbuddget - _pwkbuddget

Best Regards

[annie_liu]

This is good! It works except the difference for a decrease from 210 to  0 should be a negative number in differences..(210) since I am working with $$.  -- How would that be incorporated?

[Anonymous · Accepted solution]

Hi @annie_liu ,

I updated my sample pbix file(see the attachment), please check if that is what you want. You can update the measure [Difference] as below to get it:

Difference = 
VAR _gl =
    SELECTEDVALUE ( 'Table'[GL] )
VAR _latestwk =
    CALCULATE ( MAX ( 'Table'[Pulled Date] ), ALLSELECTED ( 'Table' ) )
VAR _preweek =
    CALCULATE (
        MAX ( 'Table'[Pulled Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Pulled Date] < _latestwk )
    )
VAR _lwkbuddget =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[GL] = _gl
                && 'Table'[Pulled Date] = _latestwk
        ),
        [Sum of Budget]
    )
VAR _pwkbuddget =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[GL] = _gl
                && 'Table'[Pulled Date] = _preweek
        ),
        [Sum of Budget]
    )
RETURN
    IF ( ISBLANK ( _lwkbuddget ), 0, _lwkbuddget )
        - IF ( ISBLANK ( _pwkbuddget ), 0, _pwkbuddget )

Best Regards