SQL CASE Statement to DAX

[AlexCassin]

I have this statement that uses a range of possible data so I dont believe i can just use a switch and im having major issues getting my head around it.

CASE WHEN JobLoggedDate > DATEADD(day, -7, GETDATE()) THEN '< 1 Week' WHEN JobLoggedDate <= DATEADD(day, -7, GETDATE()) AND JobLoggedDate > DATEADD(month, -1, GETDATE()) THEN '1 Week - 1 Month' WHEN JobLoggedDate <= DATEADD(month, -1, GETDATE()) AND JobLoggedDate > DATEADD(month, -3, GETDATE()) THEN '1 Month - 3 Months' ELSE '> 3 Months' END AS 'AgeRange'

 How do i translate this to DAX so i can set a new column in a set of returned data to use this logic?

[v-juanli-msft · Accepted solution]

Hi @AlexCassin

Try this formula to create a calculated column

'AgeRange' =
SWITCH (
    TRUE (),
    [JobLoggedDate]
        > TODAY () - 7, "< 1 Week",
    [JobLoggedDate]
        <= TODAY () - 7
        && [JobLoggedDate]
            > DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) ), "1 Week - 1 Month",
    [JobLoggedDate]
        <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
        && [JobLoggedDate]
            > DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 3, DAY ( TODAY () ) ), "1 Month - 3 Months",
    "1 Month - 3 Months"
)

Here is my test pbix

Best Regards

Maggie

[AlexCassin]

Worked like a charm! Except 1 issue, you had put down the default to "1 Month - 3 Months" instead of the "> 3 Months" I had but only a small issue 🙂 Just thought id let you know!