Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Increase in any value by each day

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)

RegardsPBIprob.PNG

 

PBIProb1.PNG

 

1 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

View solution in original post

6 REPLIES 6
HotChilli
Super User
Super User

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
Not applicable

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

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

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.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

Try a new column like

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.