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 h...
"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

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