Forum Discussion
Calculated column using DAX
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] )
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.
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]
)
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.
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) hi, gandharv
try belowbut 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.