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.

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Try something like this.

last vs previous to last = 
VAR __tbl = ALLEXCEPT( 'Table', 'Table'[partner] )
VAR __lastDate = 
MAXX(
    __tbl,
    CALCULATE( MAX( 'Table'[datemodified] ) )
)
VAR __lastLastDate = 
MAXX(
    FILTER( __tbl, [datemodified] < __lastDate ),
    CALCULATE( MAX( 'Table'[datemodified] ) )
)
RETURN 
CALCULATE(
    SUM( 'Table'[count] ), 
    TREATAS( { __lastDate }, 'Table'[datemodified] ) 
) - CALCULATE(
    SUM( 'Table'[count] ), 
    TREATAS( { __lastLastDate }, 'Table'[datemodified] ) 
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.