Help with IF DAX Measure

[leerjones85]

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

[leerjones85]

Thank You!

[shafiz_p · Accepted solution]

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