How to check a condition against Date/Time column

[Anonymous]

Hello,

I am trying to create a new measure using IF condition,

IF report[log_date] falls in Quarter 4 of the Year then calculate average of Person A,B,C else calculate average of Person A and B only.

IF(
    report[log_date].[DATE]>DATE(2021, 10, 01),
    CALCULATE(
        [average],
        'report'[NAME] IN {
            "PERSON A",
            "PERSON B",
            "PERSON C"
        }
    ),
    CALCULATE(
        [average],
        'report'[NAME] IN {
            "PERSON A",
            "PERSON B"
        }
)

For some reason, I can't get to use log_date column, It does not prepolute in the new measure creation.
Not sure how to address this, Error: "A single value for variation 'DATE' for column [log_date] in table 'report' cannot be determined. This can happen when a mesaure formula refers to a column that contains many values (I do see null and blank in there)" Could use some hints/tips please

Thanks,

[Anonymous]

That's it! Thanks Samarth 😊

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

You cant directly use log_date column inside "if", you need to add some extra dax function to use like max or selectedvalue e.g.

IF(
    Quater(Max(report[log_date]))= 4,
    CALCULATE(
        [average],
        'report'[NAME] IN {
            "PERSON A",
            "PERSON B",
            "PERSON C"
        }
    ),
    CALCULATE(
        [average],
        'report'[NAME] IN {
            "PERSON A",
            "PERSON B"
        }
)

Thanks,

Samarth