Forum Discussion

annie_liu's avatar
annie_liu
Icon for Helper I rankHelper I
2 years ago
Solved

Sum Difference between two date entries via matrix

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's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      annie_liu
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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