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:  T...
  • 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

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