Calculated column using DAX

[gandharv]

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,

[Dangar332]

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 
)

[gandharv]

Hi Dangar, Thanks for the reply. but your query doesn't seem to consider the Date Column. 

[Dangar332]

HI, @gandharv 

as per your requirnment it not necessary to use date column

try above code check you get your expected output or not?

[gandharv]

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. 

[Dangar332]

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 
)

[gandharv]

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. 

[Dangar332 · Accepted 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]

)

[gandharv]

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. 

[Dangar332]

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.

[Dangar332]

Hi, @gandharv 

please paste data here or provide data file

[gandharv]

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)