Forum Discussion
gandharv
2 years agoFrequent Visitor
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...
- 2 years ago
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] )
gandharv
2 years agoFrequent Visitor
| 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) | |