CODEWITHSUNDEEP
daxcalculated-columnsdate-math
Hell-1931
Hell-1931

Reputation: 509

Calculating number of Quarters between 2 dates in DAX, applying filter

I have the following table, tbl_Episodes (10K records):

       ClientID       AdmDate    DischDate
            54      9/20/2013    5/28/2015
            63      3/07/2013    4/12/2014 
            75      4/07/2014   12/31/2050
            .              .            .
            .              .            .

I need to create a calculated column, which will calculate the number of quarters (3 months) between the AdmDate (Patient's Admission Date) and DischDate (Patient's Discharge Date)

I am using the following DAX expression:

       # Quarters= Datediff('tbl_Episodes'[AdmDate], 
                            'tbl_Episode'[DischDate],QUARTER)

But I need to exclude the calculation if DischDate = 12/31/2050

I expect my result as:

      ClientID       AdmDate    DischDate   #Quarters
            54      9/20/2013    5/28/2015         6
            63      3/07/2013    4/12/2014         4 
            75      4/07/2014   12/31/2050         0   (or blank would work)
             .             .            .          .
             .             .            .          .

Upvotes: 2

Views: 1088

Answers (1)

sergiom
sergiom

Reputation: 4887

A solution is to add a test using the IF function, that will return a BLANK() if the condition is false and the result of the DATEDIFF if the condition is true. The condition in this case is on DischDate to differ from 12/31/2050

# Quarters =
IF(
    'tbl_Episode'[DischDate] <> VALUE("2050-12-31"),
    DATEDIFF(
        'tbl_Episodes'[AdmDate],
        'tbl_Episode'[DischDate],
        QUARTER
    )
)

Edit: added "VALUE" as correctly pointed out in comment

Upvotes: 1

Related Questions