Forum Discussion

POSPOS's avatar
POSPOS
Post Partisan
2 years ago
Solved

Incorrect values when calculating range

Hi, I have a requirement to calculate the date difference between two dates (Initial Date and today()) and then categorise them into different buckets 0-45, 46-89,90+ I am getting incorrect val...
  • AmiraBedh's avatar
    2 years ago

    In your current setup, when the data11[Initial Date] is blank, DATEDIFF returns a null value, which is not correctly handled in your Range calculation. The solution is to first check if data11[Initial Date] is blank before calculating the difference. If it's blank, you should directly assign "Blank" to the range. Otherwise, proceed with the date difference calculation.

     

     

     

    Range = 
    IF(
        ISBLANK(data11[Initial Date]), 
        "Blank",
        VAR Differnece = DATEDIFF(data11[Initial Date], TODAY(), DAY)
        RETURN
            IF(Differnece >= 0 && Differnece < 46, "0-45",
            IF(Differnece >= 46 && Differnece < 90, "46-90",
            IF(Differnece >= 90, ">90")))
    )