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, 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.