Binning a measure

[Datagulf]

I am trying to create a measure or a column that will create groups of a column. This measure is the number of days of a project that I have already established using datediff. The days vary from 1 day to 160 days. All I want is to achieve these groups and their frequencies. This was however achieved in another method here.

https://community.powerbi.com/t5/Desktop/Date-Difference-with-a-switch-between-two-dates-and-One-dat... . However, this is not working as perfectly when I add other columns like the project cost in all the projects. 
Whichever other methods is highly welcome. Thanks

[parry2k · Accepted solution]

@Datagulf see attached and tweak as you see fit.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Datagulf it is a bit unclear what you are trying to achieve. I would recommend pasting sample data with the expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Datagulf]

Thanks, @parry2k 

I have a dataset (Table1) with the following sample data. 

Project ID	Date Started	date completed	Amount used
1	04/02/2021	27/02/2021	34,533,200
2	07/02/2021		34,095,600
3	23/03/2021	07/04/2021	27,563,760
4	31/03/2021	05/05/2021	19,456,980
5	03/05/2021		13,904,710
6	01/06/2021		10,543,600

The task is to get the difference in days from start and end of the project. As you can see, some projects do not have a date completed. Hence for these ones, one should get the difference between the Start date and today so as to know how long these projects have taken before completion. 


The next task is to bin/group these projects by the time they have taken so far or they took to complete if already completed. The groups are already predetermined and are as follows.

0-30days, 30-60 days, 60 -90 days, More than 90 days. 

The ultimate solution is to have a table like this one. 

Group	project ID Frequency	Amount Used Total
0-30 days	7	100,345,230
30-60days	3	65,677,900
60-90 days	2	23,567,677
More than 90 days	0	0

I hope this is quite understandable now. Thanks.