Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Recreating Case Statement in Dax

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

Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

    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'
)

     

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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'
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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