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

     

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