Days Group DAX Function

[skazaryan]

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. 

[skazaryan]

Thank you, everyone, for the help!

[v-yiruan-msft · Accepted solution]

Hi @skazaryan，

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

[az38]

Hi @skazaryan 

try to use grouping technique as described here https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

[vivran22]

Hello @skazaryan 

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