Difference between latest and second latest values

[Anonymous]

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

partner	count	datemodified
Garmin	71	02/12/2019 22:20:27
GenericUpload	2	02/12/2019 22:20:27
Huami	52537	02/12/2019 22:20:27
Huawei	9476	02/12/2019 22:20:27
InBody	38760	02/12/2019 22:20:27
MMF	1	02/12/2019 22:20:27
Polar	2567	02/12/2019 22:20:27
SaveAssessment	69	02/12/2019 22:20:27
Softbank	11310	02/12/2019 22:20:27
Strava	1367	02/12/2019 22:20:27
Suunto	1518	02/12/2019 22:20:27
Technogym	19	02/12/2019 22:20:27
Tranggle	94	02/12/2019 22:20:27
Withings	2491	02/12/2019 22:20:27
Fitbit	876	02/12/2019 23:20:24
Garmin	71	02/12/2019 23:20:24
GenericUpload	2	02/12/2019 23:20:24
Huami	55765	02/12/2019 23:20:24
Huawei	10121	02/12/2019 23:20:24
InBody	41140	02/12/2019 23:20:24
MMF	2	02/12/2019 23:20:24
Polar	2725	02/12/2019 23:20:24
SaveAssessment	72	02/12/2019 23:20:24
Softbank	12006	02/12/2019 23:20:24
Strava	1456	02/12/2019 23:20:24
Suunto	1537	02/12/2019 23:20:24
Technogym	19	02/12/2019 23:20:24
Tranggle	94	02/12/2019 23:20:24
Withings	2579	02/12/2019 23:20:24
Fitbit	885	03/12/2019 00:20:24
Garmin	86	03/12/2019 00:20:24
GenericUpload	2	03/12/2019 00:20:24
Huami	58993	03/12/2019 00:20:24
Huawei	10766	03/12/2019 00:20:24
InBody	43520	03/12/2019 00:20:24
MMF	2	03/12/2019 00:20:24
Polar	2884	03/12/2019 00:20:24
SaveAssessment	74	03/12/2019 00:20:24
Softbank	12702	03/12/2019 00:20:24
Strava	1539	03/12/2019 00:20:24
Suunto	1556	03/12/2019 00:20:24
Technogym	19	03/12/2019 00:20:24
Tranggle	94	03/12/2019 00:20:24
Withings	2755	03/12/2019 00:20:24
Fitbit	970	03/12/2019 01:20
Garmin	116	03/12/2019 01:20
GenericUpload	4	03/12/2019 01:20
Huami	88056	03/12/2019 01:20
Huawei	16521	03/12/2019 01:20
InBody	64906	03/12/2019 01:20
MMF	5	03/12/2019 01:20
Polar	4347	03/12/2019 01:20
SaveAssessment	104	03/12/2019 01:20
Softbank	18966	03/12/2019 01:20
Strava	2288	03/12/2019 01:20
Suunto	1729	03/12/2019 01:20
Technogym	35	03/12/2019 01:20
Tranggle	94	03/12/2019 01:20
Withings	4075	03/12/2019 01:20

[v-juanli-msft · Accepted solution]

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]

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.