cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Calculate Increase in any value by each day

Hi @Lionel_Maximus ,

 

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_tZBtFFr3v...

 

Best Regards,

Dedmon Dai

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Calculate Increase in any value by each day

Try a new column like

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


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User I
Super User I

Re: Calculate Increase in any value by each day

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.

Highlighted
Frequent Visitor

Re: Calculate Increase in any value by each day

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

Highlighted
Super User I
Super User I

Re: Calculate Increase in any value by each day

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])))

 

Highlighted
Super User IV
Super User IV

Re: Calculate Increase in any value by each day

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/
Highlighted
Microsoft
Microsoft

Re: Calculate Increase in any value by each day

Hi @Lionel_Maximus ,

 

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_tZBtFFr3v...

 

Best Regards,

Dedmon Dai

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors