LOOKUPVALUE takes a very long time

[Yaa_D]

Hi All, 

I have this data set that is updated daily:

In order to calculate a daily diff of [total] for each id I've added the following columns:

total_yesterday = LOOKUPVALUE(
                         [total],
                         [file_date],[file_date]-1,
                         [Id],[Id],
                         [total])

daily_diff = [total] - [total_yesterday]

to create this:

My problem is, with ~10000 unique ids and ~30 days of data, the [total_yesterday] column takes about 10-12 minuts to be calculated.

Is this the correct way to find the daily total? or is there a better, more efficiant way to make this calculation? 

Thanks for the help!

[v-stephen-msft · Accepted solution]

Hi @Yaa_D ,

Try this

total_yesterday = var _yeasterday=CALCULATE([total],PREVIOUSDAY('Table'[file_date]))
return IF(ISBLANK(_yeasterday),[total],+_yeasterday)

daily_diff = [total]-[total_yesterday]

Best Regards,

Stephen Tao

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

[smpa01 · Accepted solution]

@Yaa_D  my take on this is to use a measure like this. In case, you have gaps in date

Measure =
VAR _currentTotal =
    SUM ( 'Table'[total value] )
VAR _visibleDate =
    MAX ( 'Table'[file_date] )
VAR _visibleId =
    MAX ( 'Table'[id] )
VAR _immediatelyPreceding =
    CALCULATE ( MAX ( 'Table'[file_date] ), 'Table'[file_date] < _visibleDate )
VAR _immediatelyPrecedingTotal =
    CALCULATE (
        SUM ( 'Table'[total value] ),
        'Table'[id] = _visibleId
            && 'Table'[file_date] = _immediatelyPreceding,
        REMOVEFILTERS ( 'Table' )
    )
RETURN
    _currentTotal - _immediatelyPrecedingTotal

[v-stephen-msft · Accepted solution]

Hi @Yaa_D ,

Try this

total_yesterday = var _yeasterday=CALCULATE([total],PREVIOUSDAY('Table'[file_date]))
return IF(ISBLANK(_yeasterday),[total],+_yeasterday)

daily_diff = [total]-[total_yesterday]

Best Regards,

Stephen Tao

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

[amitchandak]

@Yaa_D , Try like, if this can help

total_yesterday = Maxx(filter(Table, [file_date] = earlier([file_date]) && [Id] =earlier([Id])), [total])