Day to day difference in cumulative values

[K-LED]

Hi All,

I have an example of data above. Every day, the value of each custer increase.

What I need to calculate is the day to day difference between value of each customer ? and then the percentage of the increasing value ?

Thanks for your help:)

[v-shex-msft]

HI @K-LED,

You can use the following measure formula to get the day of day difference and group by current customer:

formual =
VAR currDate =
    MAX ( Table[Date] )
VAR currAmount =
    SUM ( Table[Value] )
VAR prevDate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate ),
        VALUES ( Table[Customer] )
    )
VAR prevAmount =
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = prevDate ),
        VALUES ( Table[Customer] )
    )
RETURN
    IF ( prevDate <> BLANK (), DIVIDE ( currAmount - prevAmount, prevAmount ), 1 )

Regards,

Xiaoxin Sheng

[K-LED]

@v-shex-msft Many thanks for your feedback 👍 and sorry for the delay 🙂

[K-LED]

@johnt75 thanks for your feedback😀

Sometimes I have holes in the data and I skip few days.
For example, if the current day is 03.23.2022 and the previous day is 03.20.2022, what should I do in this case to have the difference ?

With the function PREVIOUSDAY() it doesn't work.

Thanks for your help.

[johnt75]

You can use TOPN in that case, like

Prev Value =
var currentCustomer = 'Table'[Customer]
var currentDate = 'Date'[Date]
return SELECTCOLUMNS( TOPN( 1, FILTER( 'Table', 'Table'[Customer] = currentCustomer &&
'Table'[Date] < currentDate), 'Table'[Date], DESC), "@value", 'Table'[Value] )

[K-LED]

Thanks for the feedback.

With the TOPN function I have the next error message: 

A table of multiple values was supplied where a single value was expected.

[johnt75]

That would seem to indicate that you have multiple entries for some customers on the same date. If you have a unique ID on each row you can use that as a 2nd sort column in the TOPN to make sure that you only have 1 row returned. If you don't have a unique column you could add an index column using Power Query and use that.

[johnt75]

How you get the Value for the previous day depends on whether Value is a column or a measure. If its a column then you can use LOOKUPVALUE, like

Prev Value =
var currentCustomer = 'Table'[Customer]
var currentDate = 'Date'[Date]
return LOOKUPVALUE( 'Table'[Value], 'Table'[Customer], currentCustomer, 'Table'[Date], 
currentDate - 1 )

and if it is a measure you can get it like

Prev Value = CALCULATE([Value], PREVIOUSDAY('Date'[Date]))

Use the appropriate method to store the previous value in a variable and then you can work out the difference and % difference