Calculate Increase in any value by each day

[Anonymous]

Hi, 

Hope you are all doing great.

I have a table which has Stock and it's value by each day. I want to calculate the increase in each day.e.g. On Monday the stock1 price was $19 and On Tuesday it became $20. When i calculate on the front end it shows $39 that's why i want to have another column which will calculate the difference between both values e.g. On Monday Stock Price was $19 and on Tuesday it increased by $1 so the total price has now become $20. Attached images(below)

Regards

[HotChilli]

A measure like this will work because your dates are consecutive:

Cdiff = SUM(TableQ[Value]) - CALCULATE(SUM(TableQ[Value]), PREVIOUSDAY(TableQ[Date]))

Strictly speaking I wouldn't use a function like PREVIOUSDAY without a Date table but see how you get on.

Also, if posting please post data, not a picture, it aways helps.

[Anonymous]

Hi,

Thank you for your reply. I want to create a calculated column not a measure because measure will not be able to return the total value correctly. I also have a separate Date table.Please find the attached sheet you requested earlier.

Regards,

Data:

Stock Name Date Value Increment
Stock1 3/25/2020 19.5 19.5
Stock1 3/26/2020 20 0.5
Stock1 3/27/2020 23 3
Stock1 3/28/2020 25 2
Stock1 3/29/2020 26 1
Stock2 3/25/2020 51 51
Stock2 3/26/2020 53 2
Stock2 3/27/2020 53.5 0.5
Stock2 3/28/2020 55 1.5
Stock2 3/29/2020 57 2

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Would you please refer  to the following calculated column:

Increment =

VAR c = 'Table'[Value]

VAR y =

    CALCULATE (

        MAX ( 'Table'[Value] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[Stock Name ] = EARLIER ( 'Table'[Stock Name ] )

                && 'Table'[Date ]

                    = EARLIER ( 'Table'[Date ] ) - 1

        )

    )

RETURN

    c - y

For more details, please refer to https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYWPmJ_J_tZBtFFr3vP_76cBh80aY6OtmMBZUu9UE2TDvQ?e=3x5Fwa

Best Regards,

Dedmon Dai

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(ISBLANK(LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Stock Name]=EARLIER(Data[Stock Name])&&Data[Date]=EARLIER(Data[Date])-1)),Data[Stock Name],Data[Stock Name])),Data[Value],Data[Value]-LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Stock Name]=EARLIER(Data[Stock Name])&&Data[Date]=EARLIER(Data[Date])-1)),Data[Stock Name],Data[Stock Name]))

Hope this helps.

[HotChilli]

A column can be found with:

Column = TableQ[Value] - CALCULATE(SUM(TableQ[Value]), FILTER(TableQ, TableQ[Date] = EARLIER(TableQ[Date])-1 && 
                                             TableQ[Stock Name] = EARLIER(TableQ[Stock Name])))

The formula given by amitchandak really just had a typo in the comparison.

Also, on measures, this should work if using the date from date table in the visualisation, and get correct total:

MeasureCdiff2 = SUMX(TableQ,   TableQ[Value] - CALCULATE(SUM(TableQ[Value]), ALLEXCEPT(TableQ, TableQ[Stock Name]), PREVIOUSDAY(Tabledates[Date])))

[amitchandak]

Try a new column like

diff = [Value] - maxx(filter(table,[Date]<earlier([Date])-1 && [Stock Name] = earlier([Stock Name])),[Value])