Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Difference between latest and second latest values

Hi,   I have a report in Direct Query mode. There is a table which contains the count for various partners for each hour. I need a measure to get the diffrence of the latest value and the one befor...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create measures

    previous count =
CALCULATE (
    SUM ( 'Table'[count] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[partner] = MAX ( 'Table'[partner] )
            && DATEDIFF ( 'Table'[datemodified], MAX ( 'Table'[datemodified] ), HOUR ) = 1
    )
)

last datetime =
CALCULATE (
    MAX ( 'Table'[datemodified] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[partner] = MAX ( 'Table'[partner] ) )
)


last count =
CALCULATE (
    SUM ( 'Table'[count] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[partner] = MAX ( 'Table'[partner] )
            && 'Table'[datemodified] = [last datetime]
    )
)


last-(last-1) = IF(MAX('Table'[datemodified])=[last datetime],[last count]-[previous count])

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi Anonymous 

Create measures

previous count =
CALCULATE (
    SUM ( 'Table'[count] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[partner] = MAX ( 'Table'[partner] )
            && DATEDIFF ( 'Table'[datemodified], MAX ( 'Table'[datemodified] ), HOUR ) = 1
    )
)

last datetime =
CALCULATE (
    MAX ( 'Table'[datemodified] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[partner] = MAX ( 'Table'[partner] ) )
)


last count =
CALCULATE (
    SUM ( 'Table'[count] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[partner] = MAX ( 'Table'[partner] )
            && 'Table'[datemodified] = [last datetime]
    )
)


last-(last-1) = IF(MAX('Table'[datemodified])=[last datetime],[last count]-[previous count])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.