Translate SQL logic to DAX

RBS

Can someone help me adjust this SQL query to DAX :

SELECT
    CASE
        WHEN EXTRACT(DAY FROM dates) = 1 AND EXTRACT(MONTH FROM dates) = 1 THEN 'Q4 ' || TO_CHAR(EXTRACT(YEAR FROM dates) - 1)
        WHEN EXTRACT(DAY FROM dates) = 1 AND EXTRACT(MONTH FROM dates) = 4 THEN 'Q1 ' || TO_CHAR(EXTRACT(YEAR FROM dates))
        WHEN EXTRACT(DAY FROM dates) = 1 AND EXTRACT(MONTH FROM dates) = 7 THEN 'Q2 ' || TO_CHAR(EXTRACT(YEAR FROM dates))
        WHEN EXTRACT(DAY FROM dates) = 1 AND EXTRACT(MONTH FROM dates) = 10 THEN 'Q3 ' || TO_CHAR(EXTRACT(YEAR FROM dates))
        ELSE NULL
    END AS " Quarter"
FROM
    myTable

I need to adjust the quarter in a seperate column

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi2y ago

Accepted answer

I suppose you need to add a column in the table myTable :

Adjusted Quarter = 
VAR dates = [YourDateColumn]  -- Replace with your actual date column name
VAR day = DAY(dates)
VAR month = MONTH(dates)
VAR year = YEAR(dates)
RETURN
    SWITCH(
        TRUE(),
        day = 1 && month = 1, "Q4 " & FORMAT(year - 1, "####"),
        day = 1 && month = 4, "Q1 " & FORMAT(year, "####"),
        day = 1 && month = 7, "Q2 " & FORMAT(year, "####"),
        day = 1 && month = 10, "Q3 " & FORMAT(year, "####"),
        BLANK()
    )