Recreating Case Statement in Dax

[Anonymous]

Hi,

I'm trying to recreate the following case statement in dax

'FY ' + cast(CASE  WHEN MONTH(jb.date_comp) > 3 THEN YEAR(jb.date_comp) + 1 ELSE YEAR(jb.date_comp)  END as varchar) AS COMP_FISCAL_YEAR_TEXT
,  CASE MONTH(jb.date_comp)  
    when 1 then 'P10 Jan'
    when 2 then 'P11 Feb'
    when 3 then 'P12 Mar'
    when 4 then 'P01 Apr'
    when 5 then 'P02 May'
    when 6 then 'P03 Jun'
    when 7 then 'P04 Jul'
    when 8 then 'P05 Aug'
    when 9 then 'P06 Sep'
    when 10 then 'P07 Oct'
    when 11 then 'P08 Nov'
    when 12 then 'P09 Dec'
    ELSE '0'END AS COMP_FISCAL_PERIOD

This produces two additional colunms

[v-shex-msft]

Hi @Anonymous,

parry2k's formula seems well. If you do not want to hardcode result with 'switch' function, you can try to use the following calculate column formula to dynamically generate tag based on conditions:

COMP_FISCAL_PERIOD =
VAR monthnum =
    MONTH ( [date_comp] )
VAR fm =
    IF ( monthnum <= 12, IF ( monthnum - 3 <= 0, monthnum - 3 + 12, monthnum - 3 ) )
RETURN
    IF (
        fm <> BLANK (),
        IF ( LEN ( fm ) < 2, "P0", "P" ) & fm
            & FORMAT ( [date_comp], " MMM" ),
        "0"
    )

Regards,

Xiaoxin Sheng

[parry2k · Accepted solution]

@Anonymous add new column with following expresssion

Would appreciate Kudos 🙂 if my solution helped.

COMP_FISCAL_YEAR_TEXT = 
VAR __month = MONTH(jb[date_comp])
VAR __year = YEAR(jb[date_comp]) 
RETURN
'FY ' & IF ( __month > 3, __year + 1, __year )

COMP_FISCAL_PERIOD =  
SWITCH (  MONTH(jb[date_comp])  ,
    1 , 'P10 Jan'
    2 , 'P11 Feb'
    3 , 'P12 Mar'
    4 , 'P01 Apr'
    5 , 'P02 May'
    6 , 'P03 Jun'
    7 , 'P04 Jul'
    8 , 'P05 Aug'
    9 , 'P06 Sep'
    10 , 'P07 Oct'
    11 , 'P08 Nov'
    12 , 'P09 Dec',
    '0'
)