Forum Discussion

Emmy66's avatar
Emmy66
Helper V
5 years ago
Solved

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.

 

 

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    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's avatar
    Emmy66
    5 years ago

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

8 Replies

    • Emmy66's avatar
      Emmy66
      Helper V
      5 years ago

      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's avatar
        ChrisMendoza
        Resident Rockstar
        5 years ago

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

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    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's avatar
      Emmy66
      Helper V
      5 years ago

      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's avatar
        Icey
        Community Support
        5 years ago

        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.