topic Re: Calculated column using DAX in DAX Commands and Tips

Calculated column using DAX

gandharv — Thu, 21 Dec 2023 12:01:45 GMT

Hi Guys,

I need urgent help with the DAX function to calculate column H of the below dummy data table in Power BI.

Important to consider :

The Formula should consider Column A, B, C, and D as unique for each row, since as you can see that column H is calculated based on value of a column G in the current row + value of column H in the earlier row (within same model_run, category, date, but earlier time stamp).

Appreciate your help!

Best Regards,

Re: Calculated column using DAX

Dangar332 — Thu, 21 Dec 2023 12:50:41 GMT

Hi, gandharv

try below

column = var a = tablename[model_run] var b = tablename[category] var c = tablename[time] var d = tablename[cost]-tablename[price] return sumx( filter( tablename, tablename[model_run]=a && tablename[category]=b && tablename[time]<= c ), d )

Re: Calculated column using DAX

gandharv — Thu, 21 Dec 2023 13:14:30 GMT

Hi Dangar, Thanks for the reply. but your query doesn't seem to consider the Date Column.

Re: Calculated column using DAX

Dangar332 — Thu, 21 Dec 2023 13:22:29 GMT

HI, gandharv

as per your requirnment it not necessary to use date column

try above code check you get your expected output or not?

Re: Calculated column using DAX

gandharv — Thu, 21 Dec 2023 13:27:23 GMT

Sorry mate, if I misrepresented the Date column by keeping it the same date for all rows. But in reality, I have dates from 1/1/23 to 31/12/26, for each Category with Timestamp of 24 hour for each date.

Re: Calculated column using DAX

Dangar332 — Thu, 21 Dec 2023 13:30:27 GMT

Hi, gandharv

column = var a = tablename[model_run] var b = tablename[category] var c = tablename[time] var d = tablename[cost]-tablename[price] var e = tablename[date] return sumx( filter( tablename, tablename[model_run]=a && tablename[category]=b && tablename[time]<= c && tablename[time]=e ), d )

Re: Calculated column using DAX

gandharv — Thu, 21 Dec 2023 14:31:01 GMT

Hi, thanks again, I applied it but the result is wrong, In below screenshot, the Carry forward column has the correct result (manually done in excel) and the calculated column is using ur DAX.

Re: Calculated column using DAX

Dangar332 — Thu, 21 Dec 2023 14:47:05 GMT

Hi, gandharv

in above you mention price-cost and you tack cost-price(f2-e2)

so i take as a cost-price(f2-e2) so look at these

try below code with some changes

column= var a = tablename[model_run] var b = tablename[category] var c = tablename[time] var e = tablename[date] return sumx( filter( tablename, tablename[model_run]=a && tablename[category]=b && tablename[time]<= c && tablename[time]=e ), tablename[cost]-tablename[price] )

Re: Calculated column using DAX

gandharv — Wed, 03 Jan 2024 11:28:30 GMT

Hi Dangar,

Thank you for your help earlier. I have realised some changes in my original dataset, herewith am attaching a screenshot of dummy data which is the actual representation of my real data. Can you pls help me modify the DAX function accordingly? I have manually calculated in Excel for your clear understanding and mentioned the condition as well.

Re: Calculated column using DAX

Dangar332 — Wed, 03 Jan 2024 13:47:58 GMT

Hi, gandharv

please paste data here or provide data file

Re: Calculated column using DAX

gandharv — Wed, 03 Jan 2024 13:56:06 GMT

Model_Run	Area	Date	Time	Price	Cost	Price - Cost (Value)	Carry forward (Value)	Carry forward (Formula)
1	A	1/1/2023	00:00	100	150	-50	0	=IF(G2 < 0, 0, G2)	Condition for the column: Carry Forward shouldn't be less than 0
1	A	1/1/2023	00:15	200	150	50	50	=IF(G3+H2 < 0, 0, G3+H2)
1	A	1/1/2023	00:30	250	150	100	150	=IF(G4+H3 < 0, 0, G4+H3)
1	A	1/1/2023	00:45	100	150	-50	100	=IF(G5+H4 < 0, 0, G5+H4)
1	A	1/1/2023	01:00	50	150	-100	0	=IF(G6+H5 < 0, 0, G6+H5)
1	A	1/1/2023	01:15	30	150	-120	0	=IF(G7+H6 < 0, 0, G7+H6)
1	A	2/2/2023	00:00	100	150	-50	0	=IF(G8 < 0, 0, G8)	Condition: Running total should restart as the Date changes
1	A	2/2/2023	00:15	200	150	50	50	=IF(G9+H8 < 0, 0, G9+H8)
1	A	2/2/2023	00:30	100	150	-50	0	=IF(G10+H9 < 0, 0, G10+H9)
1	B	1/1/2023	00:00	100	150	-50	0	=IF(G11 < 0, 0, G11)	Condition: Running total should restart as the Area and Date changes
1	B	1/1/2023	00:15	200	150	50	50	=IF(G12+H11 < 0, 0, G12+H11)
1	B	1/1/2023	00:30	250	150	100	150	=IF(G13+H12 < 0, 0, G13+H12)
2	A	1/1/2023	00:00	100	150	-50	0	=IF(G14 < 0, 0, G14)	Condition: Running total should restart as the Model_Run, Area and Date changes
2	A	1/1/2023	00:15	200	150	50	50	=IF(G15+H14 < 0, 0, G15+H14)
2	A	1/1/2023	00:30	250	150	100	150	=IF(G16+H15 < 0, 0, G16+H15)
2	A	1/1/2023	00:45	100	150	-50	100	=IF(G17+H16 < 0, 0, G17+H16)
2	A	1/1/2023	01:00	50	150	-100	0	=IF(G18+H17 < 0, 0, G18+H17)
2	A	1/1/2023	01:15	30	150	-120	0	=IF(G19+H18 < 0, 0, G19+H18)
2	A	2/2/2023	00:00	100	150	-50	0	=IF(G20 < 0, 0, G20)	Condition: Running total should restart as the Date changes
2	A	2/2/2023	00:15	200	150	50	50	=IF(G21+H20 < 0, 0, G21+H20)
2	A	2/2/2023	00:30	100	150	-50	0	=IF(G22+H21 < 0, 0, G22+H21)
2	B	1/1/2023	00:00	100	150	-50	0	=IF(G23 < 0, 0, G23)	Condition: Running total should restart as the Area and Date changes
2	B	1/1/2023	00:15	200	150	50	50	=IF(G24+H23 < 0, 0, G24+H23)
2	B	1/1/2023	00:30	250	150	100	150	=IF(G25+H24 < 0, 0, G25+H24)

Re: Calculated column using DAX

Dangar332 — Wed, 03 Jan 2024 16:35:43 GMT

hi, gandharv

try below

but cross check it before move ahead

Column = var a = FILTER('Table (2)', 'Table (2)'[Model_Run]=EARLIER('Table (2)'[Model_Run]) && 'Table (2)'[Area] = EARLIER('Table (2)'[Area]) && 'Table (2)'[Date] = EARLIER('Table (2)'[Date]) && 'Table (2)'[Time]<= EARLIER('Table (2)'[Time]) ) var b = MINX('Table (2)','Table (2)'[Time]) var d = MINX(FILTER('Table (2)','Table (2)'[Time]=b),'Table (2)'[Price - Cost (Value)]) var c = SUMX(a,'Table (2)'[Price - Cost (Value)])-d return IF(c<0,0,c)

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.