Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Tableau to DAX

Hi, I need help converting these two calculated fields from Tableau to DAX.

 

Freeze Date

IF ENDSWITH([Academic Period Id],'9') THEN

DATEPARSE('MMM-dd-yyyy','Aug-15-'+LEFT([Academic Period Id],4))

ELSE DATEPARSE('MMM-dd-yyyy','Jan-15-'+LEFT([Academic Period Id],4))

END

 

Resident at Freeze

IF [Resident Length] > 0

AND [Academic Period Id] = '202009' THEN 'Y'

ELSEIF [Resident Length] > 0

AND [Resident Start Date] <= [Freeze Date] AND [Resident End Date] >= [Freeze Date]

THEN 'Y' ELSE 'N' END

 

Thanks!

Need Help
Show and Tell
Tips and Tricks
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Anonymous Please try this:-

    Resident at Freeze =
IF (
    [Resident Length] > 0
        && [Academic Period Id] = 202009,
    "Y",
    IF (
        [Resident Length] > 0
            && [Resident Start Date] <= [Freeze Date]
            && [Resident End Date] >= [Freeze Date],
        "Y",
        "N"
    )
)

3 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    If you could provide some sample data in text format(not a screenshot) with expected output then it would be more helpful but you could try this:-

    Freeze Date =
VAR _endswith =
    RIGHT ( [Academic Period Id], 1 )
VAR _startwith =
    LEFT ( [Academic Period Id], 4 )
RETURN
    IF (
        _endswith = "9",
        FORMAT ( DATE ( LEFT ( [Academic Period Id], 4 ), 08, 15 ), "MMM-dd-yyyy" ),
        FORMAT ( DATE ( LEFT ( [Academic Period Id], 4 ), 01, 15 ), "MMM-dd-yyyy" )
    )

     

    Resident at Freeze =
IF (
    [Resident Length] > 0
        && [Academic Period Id] = "202009",
    "Y",
    IF (
        [Resident Length] > 0
            && [Resident Start Date] <= [Freeze Date]
            && [Resident End Date] >= [Freeze Date],
        "Y",
        "N"
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      For Resident at Freeze, it throws an error:

       

      DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        Anonymous Please try this:-

        Resident at Freeze =
IF (
    [Resident Length] > 0
        && [Academic Period Id] = 202009,
    "Y",
    IF (
        [Resident Length] > 0
            && [Resident Start Date] <= [Freeze Date]
            && [Resident End Date] >= [Freeze Date],
        "Y",
        "N"
    )
)