Forum Discussion

gandharv's avatar
gandharv
Frequent Visitor
2 years ago
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...
  • Dangar332's avatar
    Dangar332
    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]

)

     

     

Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

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's avatar
gandharv
Frequent Visitor
2 years ago

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's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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's avatar
      gandharv
      Frequent Visitor
      2 years ago

      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's avatar
        Dangar332
        Resident Rockstar
        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]

)