Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Below is a sample data that is being used.
The requirement is to calculate the remaining for each row by substracting it from the previous line.
Basically for 10 Oct it is easily calculated, however for 18 OCT we should take 3,445,900 - 1,000,00 as we had 2 purchased on 18 Oct
Is there a way to achieve this on power BI?
Vessel Name | Purchase date | Required | Purchased | remaining |
RT 01 | 10-Oct-22 | 3,645,900 | 200,000 | 3,445,900 |
RT 01 | 18-Oct-22 | 500,000 | ||
RT 01 | 19-Oct-22 | 1,400,000 | ||
RT 01 | 18-Oct-22 | 500,000 | ||
RT 01 | 29-Mar-22 | 400,000 | ||
RT 01 | 24-Nov-22 | 645,000 | ||
RU 05 | 16-Mar-23 | 3,459,250 | 1,500,000 | 1,959,250 |
RU 05 | 03-Apr-23 | 200,000 | ||
RU 05 | 03-Apr-23 | 400,000 | ||
RU 05 | 04-Apr-23 | 500,000 | ||
MV 05 | 11-Apr-23 | 100,000 | ||
MV 05 | 11-Apr-23 | 1,000,000 |
Solved! Go to Solution.
Then, something like on the screenshot below. The table visual allows sorting by multiple columns if you hold SHIFT key.
In plain text:
Remaining =
VAR CurDate = [Purchase date]
VAR CurVessel = [Vessel Name]
VAR _tbl = FILTER ( Data, [Vessel Name] = CurVessel && [Purchase date] <= CurDate )
RETURN SUMX ( FILTER ( Data, [Vessel Name] = CurVessel ) , [Required] ) - SUMX ( _tbl, [Purchased] )
Best Regards,
Alexander
Hi @ashraf_K,
I'd solve your task with a calculation column like this. The question is how to treat the date 29.03.2022, which appears somewhere in the middle.
In plain text:
Remaining =
VAR CurDate = [Purchase date]
VAR CurVessel = [Vessel Name]
VAR _tbl = FILTER ( Data, [Vessel Name] = CurVessel && [Purchase date] <= CurDate )
RETURN SUMX ( _tbl, [Required] ) - SUMX ( _tbl, [Purchased] )
Best Regards,
Alexander
Ideally it should be sorted by purchase date so that we can show how the purchase is progressing through time
Then, something like on the screenshot below. The table visual allows sorting by multiple columns if you hold SHIFT key.
In plain text:
Remaining =
VAR CurDate = [Purchase date]
VAR CurVessel = [Vessel Name]
VAR _tbl = FILTER ( Data, [Vessel Name] = CurVessel && [Purchase date] <= CurDate )
RETURN SUMX ( FILTER ( Data, [Vessel Name] = CurVessel ) , [Required] ) - SUMX ( _tbl, [Purchased] )
Best Regards,
Alexander
Yes, that does solve the issues.
Thank you!
User | Count |
---|---|
70 | |
43 | |
21 | |
21 | |
13 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
25 |