Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Tableau formula to DAX

Hi Team,

 

I am not sure how to write this formula in DAX as it is used in tableau to create a period start date.

 

DATE(
STR([P_Fiscal Year])+ "-"
+
STR(
IF [P_Fiscal Period ] <= 9 THEN "0"+STR([P_Fiscal Period ])
ELSE STR([P_Fiscal Period ]) END
) + "-"
+ "01")
Help

1 Reply

  • adudani's avatar
    adudani
    Icon for Memorable Member rankMemorable Member
    2 years ago

    hi Anonymous ,

     

    try creating a calculated column in your table with the following measure.

     

    Assuming data types of Year and Fiscal Period = Int/wholenumber  

     

     

     

    Period Start Date = 
FORMAT(
DATE( 
    [P_Fiscal Year],
    IF('Table'[P_FiscalPeriod]<=9,"0"&[P_FiscalPeriod],[P_FiscalPeriod]),
    01),
    "yyyy-mm-dd"
)

     

     

     

    Sample output is shown.

     

     you could also just use:

     

    Period Start Date = 
FORMAT(
DATE( 
    [P_Fiscal Year],
    'Table'[P_FiscalPeriod],
    01),
    "yyyy-mm-dd"
)

     

    as format handles the "0" in the if condition 



    let me know if this works for you.

     

    if it doesn't kindly provide sample inputs / specify data types.