Forum Discussion
Solved
Calculated column using DAX
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 un...
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)