Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |