Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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
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
)
Hi Dangar, Thanks for the reply. but your query doesn't seem to consider the Date Column.
HI, @gandharv
as per your requirnment it not necessary to use date column
try above code check you get your expected output or not?
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.
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
)
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.
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.
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) | |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
44 | |
16 | |
12 |