topic Re: Help with IF DAX Measure in DAX Commands and Tips

Help with IF DAX Measure

leerjones85 — Wed, 30 Oct 2024 03:03:28 GMT

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

Re: Help with IF DAX Measure

shafiz_p — Wed, 30 Oct 2024 03:55:46 GMT

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

Re: Help with IF DAX Measure

leerjones85 — Wed, 30 Oct 2024 04:07:37 GMT

Thank You!