topic Re: Running Difference per day and name in DAX Commands and Tips

Running Difference per day and name

ashraf_K — Tue, 10 Oct 2023 08:47:51 GMT

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

Re: Running Difference per day and name

barritown — Tue, 10 Oct 2023 10:20:57 GMT

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

My YouTube vlog in English

My YouTube vlog in Russian

Re: Running Difference per day and name

ashraf_K — Tue, 10 Oct 2023 10:26:05 GMT

Ideally it should be sorted by purchase date so that we can show how the purchase is progressing through time

Re: Running Difference per day and name

barritown — Tue, 10 Oct 2023 10:38:01 GMT

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

My YouTube vlog in English

My YouTube vlog in Russian

Re: Running Difference per day and name

ashraf_K — Tue, 10 Oct 2023 10:53:24 GMT

Yes, that does solve the issues.

Thank you!