In table complex comparison to derive new column

[Anonymous]

Have the following table data

usageDate	userName	productName	usageHours	tokensConsumed
1/1/2020	joe	a	5	4
1/2/2020	john	b	22	5
1/3/2020	joe	a	2	4
1/1/2020	john	b	22	5
1/2/2020	john	a	22	4
1/2/2020	john	b	4	5

I'm trying to add a column with the following criteria, but can't figure out how to do it... 

Value is:

- The number of tokens consumed, in the previous day, by the same user id (if exists), in the same product (if exists), if previous day hours > 20 (if exists)

So the desired result would be something like this (notes column added to explain value, don't expect this column)

usageDate	userName	productName	usageHours	tokensConsumed	previousTokens	notes
1/1/2020	joe	a	5	4	0	no usage at all by joe on 12/31/19
1/2/2020	john	b	5	5	5	john used product b for >20 hrs on 1/1/20
1/3/2020	joe	a	2	4	0	no usage at all by joe on 1/2/20
1/1/2020	john	b	22	5	0	no usage at all by john on 12/31/19
1/2/2020	john	a	22	4	0	john did not use product a on 1/1/20
1/3/2020	john	b	4	5	0	john used product b on 1/2 but not for more than >20 hrs

is this kind of inline comparison possible? or maybe i have to create some temporary tables to use?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can create a measure to get the expected result:

Previous Token = 
VAR _currentdate =
    SELECTEDVALUE ( 'Table'[usageDate] )
VAR _currentname =
    SELECTEDVALUE ( 'Table'[userName] )
VAR _currentproduct =
    SELECTEDVALUE ( 'Table'[productName] )
VAR _previoushour =
    CALCULATE (
        MAX ( 'Table'[usageHours] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [usageDate] = _currentdate - 1
                && [userName] = _currentname
                && [productName] = _currentproduct
        )
    )
VAR _previoustoken =
    CALCULATE (
        MAX ( 'Table'[tokensConsumed] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [usageDate] = _currentdate - 1
                && [userName] = _currentname
                && [productName] = _currentproduct
        )
    )
RETURN
    IF ( _previoushour > 20, _previoustoken, 0 )

Here is the sample file hopes to help you, please try it: PBIX 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.