Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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.
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])))
Try a new column like
diff = [Value] - maxx(filter(table,[Date]<earlier([Date])-1 && [Stock Name] = earlier([Stock Name])),[Value])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |