Forum Discussion

leerjones85's avatar
leerjones85
Frequent Visitor
1 year ago
Solved

Help with IF DAX Measure

Hi,

 

I am new to Power BI and have got stuck when creating a measure.

 

I have a fact table the following columns

 

'FACT_METRICS'[DATE]

'FACT_METRICS'[ACTUAL]

'FACT_METRICS'[TARGET]

 

I have a dimension table with the following

 

'DIM_DATE'[DATETIME]

'DIM_DATE'[DAY_MINUS]

 

I am wanting to create a measure than returns the following

 

IF 'FACT_METRICS'[DATE] < 'DIM_DATE'[DATETIME] WHERE 'DIM_DATE'[DAY_MINUS] = "Today" THEN CALCULATE(SUM('FACT_METRICS'[ACTUAL]) - SUM('FACT_METRICS'[TARGET])) ELSE SUM('FACT_METRICS'[TARGET])

 

Any help is much appreciated,

 

Thanks

"DAX "
"if statement"
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi leerjones85 It sounds like you’re trying to create a measure that compares dates and calculates different sums based on the condition. Here’s how you can achieve this in DAX:

    To get today's date:

    TodayDate = 
CALCULATE(
    MAX('DIM_DATE'[DATETIME]),
    'DIM_DATE'[DAY_MINUS] = "Today"
)

    Main measure:

    Actual vs Target Measure = 
IF(
    MAX('FACT_METRICS'[DATE]) < [TodayDate],
    CALCULATE(
        SUM('FACT_METRICS'[ACTUAL]) - SUM('FACT_METRICS'[TARGET])
    ),
    SUM('FACT_METRICS'[TARGET])
)

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

2 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi leerjones85 It sounds like you’re trying to create a measure that compares dates and calculates different sums based on the condition. Here’s how you can achieve this in DAX:

    To get today's date:

    TodayDate = 
CALCULATE(
    MAX('DIM_DATE'[DATETIME]),
    'DIM_DATE'[DAY_MINUS] = "Today"
)

    Main measure:

    Actual vs Target Measure = 
IF(
    MAX('FACT_METRICS'[DATE]) < [TodayDate],
    CALCULATE(
        SUM('FACT_METRICS'[ACTUAL]) - SUM('FACT_METRICS'[TARGET])
    ),
    SUM('FACT_METRICS'[TARGET])
)

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz