Grouping data into ranges

[Emmy66]

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.

Group	OverdueProjects
0-30 days	50
31-40 days	200
41-60 days	88
61-90 days	376
>90 days	69

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

[Icey · Accepted solution]

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 ) )

Best regards

Icey

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

[Icey]

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] ) ) )

Best Regards,

Icey

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

[Emmy66]

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 · Accepted solution]

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 ) )

Best regards

Icey

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

[Emmy66 · Accepted solution]

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

[ChrisMendoza]

@Emmy66 -

You can add a column using SWITCH

[Emmy66]

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. 

[ChrisMendoza]

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

[Emmy66]

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