cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Emmy66
Helper III
Helper III

Grouping data into ranges

Hi,

 

I intend to group my projects by number of days the are overdue. E.g. 0-30 days, 31-40 days, 41-60 days, 61-90 days, >90 days.

Currently I have a field TargetDate. The group above will be based on the DateDiff between TargetDate & User selected date from the Date slicer. The OverdueProjects = count(projectId).

Below is an illustration of the expected output.

 
GroupOverdueProjects
0-30 days50
31-40 days200
41-60 days88
61-90 days376
>90 days69

 

I have attached the link to the file. Any help on this will be appreciated.

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Emmy66 ,

 

Try this:

Measure 2 = 
VAR SelectedDate =
    SELECTEDVALUE ( Dates[Date], TODAY () )
VAR t =
    ADDCOLUMNS (
        ALL ( Project ),
        "Group_",
            SWITCH (
                TRUE (),
                SelectedDate - [TargetDate] <0, "Active",
                SelectedDate - [TargetDate] <= 30, "0-30 days",
                SelectedDate - [TargetDate] <= 40, "31-40 days",
                SelectedDate - [TargetDate] <= 60, "41-60 days",
                SelectedDate - [TargetDate] <= 90, "61-90 days",
                "> 90 days"
            )
    )
RETURN
    COUNTROWS ( FILTER ( t, [Group_] = SELECTEDVALUE ( 'Group'[Group] ) && [status] = 1 ) )

groups.gif

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

View solution in original post

8 REPLIES 8
Icey
Community Support
Community Support

Hi @Emmy66 ,

 

Create a measure like so:

Measure =
VAR SelectedDate =
    SELECTEDVALUE ( Dates[Date], TODAY () )
VAR t =
    ADDCOLUMNS (
        Project,
        "Group_",
            SWITCH (
                TRUE (),
                SelectedDate - [TargetDate] <= 30, "0-30 days",
                SelectedDate - [TargetDate] <= 40, "31-40 days",
                SelectedDate - [TargetDate] <= 60, "41-60 days",
                SelectedDate - [TargetDate] <= 90, "61-90 days",
                "> 90 days"
            )
    )
RETURN
    COUNTROWS ( FILTER ( t, [Group_] = SELECTEDVALUE ( 'Group'[Group] ) ) )

group.JPG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Icey  my apologies for the late response. Thanks for providing the solution which worked like charm, but one thing I didn't include in my request is that I would only want to consider projects with status = 1 and also, an additional condition to include in the range, projects whose targetDates are greater than the SelectedDate i.e. SelectedDate - TargetDate if negative to be classed as Active. Any idea how I can include them in the query please? Thanks in advance for your assistance.

Icey
Community Support
Community Support

Hi @Emmy66 ,

 

Try this:

Measure 2 = 
VAR SelectedDate =
    SELECTEDVALUE ( Dates[Date], TODAY () )
VAR t =
    ADDCOLUMNS (
        ALL ( Project ),
        "Group_",
            SWITCH (
                TRUE (),
                SelectedDate - [TargetDate] <0, "Active",
                SelectedDate - [TargetDate] <= 30, "0-30 days",
                SelectedDate - [TargetDate] <= 40, "31-40 days",
                SelectedDate - [TargetDate] <= 60, "41-60 days",
                SelectedDate - [TargetDate] <= 90, "61-90 days",
                "> 90 days"
            )
    )
RETURN
    COUNTROWS ( FILTER ( t, [Group_] = SELECTEDVALUE ( 'Group'[Group] ) && [status] = 1 ) )

groups.gif

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

View solution in original post

Hi @Icey thanks alot for the solution. It worked perfectly well. Thank you once again for your assistance. Very much appreciated. 

ChrisMendoza
Super User I
Super User I

@Emmy66 -

You can add a column using SWITCH

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Thanks @ChrisMendoza for your prompt response. The challenge is with the date selected from the slicer which can be any day. The idea is to give the user the choice of selecting any date from the Date picker. Where you have Today() in the query should be a user selected date rather than been hardcoded. I hope you understand what I intend to achieve. 

@Emmy66 - I haven't found anything useful. Maybe someone else has got some experience on this.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Thanks @ChrisMendoza , I do hope someone else will assist.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.