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 before. Due to limitations in direct query I am unable to use teh Rank function properly. Can anyone help. 

 

I want a difference of latest and latest-1 value for each partner

 

partnercountdatemodified
Garmin7102/12/2019 22:20:27
GenericUpload202/12/2019 22:20:27
Huami5253702/12/2019 22:20:27
Huawei947602/12/2019 22:20:27
InBody3876002/12/2019 22:20:27
MMF102/12/2019 22:20:27
Polar256702/12/2019 22:20:27
SaveAssessment6902/12/2019 22:20:27
Softbank1131002/12/2019 22:20:27
Strava136702/12/2019 22:20:27
Suunto151802/12/2019 22:20:27
Technogym1902/12/2019 22:20:27
Tranggle9402/12/2019 22:20:27
Withings249102/12/2019 22:20:27
Fitbit87602/12/2019 23:20:24
Garmin7102/12/2019 23:20:24
GenericUpload202/12/2019 23:20:24
Huami5576502/12/2019 23:20:24
Huawei1012102/12/2019 23:20:24
InBody4114002/12/2019 23:20:24
MMF202/12/2019 23:20:24
Polar272502/12/2019 23:20:24
SaveAssessment7202/12/2019 23:20:24
Softbank1200602/12/2019 23:20:24
Strava145602/12/2019 23:20:24
Suunto153702/12/2019 23:20:24
Technogym1902/12/2019 23:20:24
Tranggle9402/12/2019 23:20:24
Withings257902/12/2019 23:20:24
Fitbit88503/12/2019 00:20:24
Garmin8603/12/2019 00:20:24
GenericUpload203/12/2019 00:20:24
Huami5899303/12/2019 00:20:24
Huawei1076603/12/2019 00:20:24
InBody4352003/12/2019 00:20:24
MMF203/12/2019 00:20:24
Polar288403/12/2019 00:20:24
SaveAssessment7403/12/2019 00:20:24
Softbank1270203/12/2019 00:20:24
Strava153903/12/2019 00:20:24
Suunto155603/12/2019 00:20:24
Technogym1903/12/2019 00:20:24
Tranggle9403/12/2019 00:20:24
Withings275503/12/2019 00:20:24
Fitbit97003/12/2019 01:20
Garmin11603/12/2019 01:20
GenericUpload403/12/2019 01:20
Huami8805603/12/2019 01:20
Huawei1652103/12/2019 01:20
InBody6490603/12/2019 01:20
MMF503/12/2019 01:20
Polar434703/12/2019 01:20
SaveAssessment10403/12/2019 01:20
Softbank1896603/12/2019 01:20
Strava228803/12/2019 01:20
Suunto172903/12/2019 01:20
Technogym3503/12/2019 01:20
Tranggle9403/12/2019 01:20
Withings407503/12/2019 01:20
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.

2 Replies

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

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