Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Days Group DAX Function

Hi, Can you please help me with DAX formulas, I kinda stuck. 

 

I have START Date and END Date and need to calculate Days Between, I used this function below to create new column:

Days = DATEDIFF([START_DATE],[END_DATE],DAY)
Then I need to group them into categories, this is what I Use to create a new column:
Days Group = if([Days] = 0, "0 Day" , if([Days] = 1, "1 Day", if([Days]  = 2, "2 Days", if([Days] = 3, "3 Days", if([Days] = 4, "4 Days", if([Days] = 5, "5 Days", if([Days] >= 6, "6+ Days", IF(ISBLANK([Days]),BLANK(), "Pending" ))))))))
The problem is, that all my Blank Values capchuring under 0 Days Categorie, insted of Pendings. 
What should I fix in order to get me blanks under "Pendings"?
Also, my new column days is a number type, but if I change it to text then "pending" is working, but my other groups are getting messy. 
 
Please advice. 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous，

    Please try to create one calculated column as below:

    Days Group = IF ( ISBLANK ( [Days] ), "Pending", IF ( [Days] >= 6, "6+", [Days] ) & " Days" )

    If the above formula is not applicable in your scenario, could you please explain which condition can be grouped as " Pending "?

     

    Best Regards

    Rena

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous，

    Please try to create one calculated column as below:

    Days Group = IF ( ISBLANK ( [Days] ), "Pending", IF ( [Days] >= 6, "6+", [Days] ) & " Days" )

    If the above formula is not applicable in your scenario, could you please explain which condition can be grouped as " Pending "?

     

    Best Regards

    Rena

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Hello Anonymous 

     

    I would recommend to use Power Query for this:

     

    Add a custom column:

     

    Change the column type to number:

     

    Add a conditional column:

     

    Result:

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you, everyone, for the help!