Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
Hi @Icey thanks alot for the solution. It worked perfectly well. Thank you once again for your assistance. Very much appreciated.
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.
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.
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.
Hi @Icey thanks alot for the solution. It worked perfectly well. Thank you once again for your assistance. Very much appreciated.
@Emmy66 -
You can add a column using SWITCH
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.
Proud to be a Super User!