How to create a dynamic ageing of dates and then group based on age

[kamiluc]

Hi there,

I'm trying to create a visual that provides status of actions based on a user selected baseline date, it should look like this:

Each action has a due date, status and date completed (if completed). I created a calendar table, not related to anything, added a slicer and created a measure which picks up the selected baseline date:

Baseline Date = CALCULATE( MAX('Baseline Calendar'[Date]), ALLSELECTED('Baseline Calendar'[Date]))

I'm then able to calculate the age of each action in another measure, and when added to a table visual it gives me the correct info:

Action age = DATEDIFF([Baseline Date], MAX(RPT_IAA_Findings_Actions[Agreed Due Date]),DAY)

Where I'm getting stuck is in the creation of the visual because I need to group the actions based on overdue, <60, 61-120, 121-180, 181-360, >360.  I can't use a calculated field because it will not respond dynamically to the date selection, and I tried the measure below and it didn't work (I can't even put it as a x-axis in my bar chart).

Action Status (Baseline Date) =

    IF( [Action age] < 0, "Overdue",

        IF([Action age] <= 60, "Due within 60 days",

            IF([Action age] <= 120, "Due between 60 and 120 days",

                IF([Action age] <= 180, "Due between 120 and 180 days",

                    IF([Action age] <= 360, "Due between 180 and 360 days",

                        "Due over 360 days"

                    )

                )

            )

        )

    )

I'd also need to build a logic to account for the close date status on the baseline date, but I think if I can figure out the above I should be fine.

Example data:

Date Selected: Baseline Date = 10/7/2022

Action	Due Date	Status	Close Date	Action Age
A	31/12/2022	Open		171
B	30/6/2022	Closed	31/7/2022	-10
C	30/6/2022	Closed	31/5/2022	-10
D	30/6/2022	Open		-10

Any suggestions on how to achieve this?

[DataInsights · Accepted solution]

@kamiluc,

Try this solution.

1. Create a disconnected table with the action groups. Sort the Group column by the Index column.

2. Create the measure below.

Action Status (Baseline Date) =
SWITCH (
    SELECTEDVALUE ( 'Action Group'[Group] ),
    "Overdue",
        COUNTROWS (
            FILTER (
                RPT_IAA_Findings_Actions,
                DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) < 0
            )
        ),
    "<60",
        COUNTROWS (
            FILTER (
                RPT_IAA_Findings_Actions,
                DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) >= 0
                    && DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) < 60
            )
        ),
    "60-119",
        COUNTROWS (
            FILTER (
                RPT_IAA_Findings_Actions,
                DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) >= 60
                    && DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) < 120
            )
        ),
    "120-179",
        COUNTROWS (
            FILTER (
                RPT_IAA_Findings_Actions,
                DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) >= 120
                    && DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) < 180
            )
        ),
    "180-359",
        COUNTROWS (
            FILTER (
                RPT_IAA_Findings_Actions,
                DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) >= 180
                    && DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) < 360
            )
        ),
    COUNTROWS (
        FILTER (
            RPT_IAA_Findings_Actions,
            DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) >= 360
        )
    )
)

3. Add the Group column in Action Group to the X-axis, and the measure above to the Y-axis:

[kamiluc]

Thank you @DataInsights .  This works for the graph.  A follow on question is that I have a table listing the actions on the same page, and would like to make it interact with the graph, meaning that when I select a bar, for example "Overdue", it'd show all overdue actions. Is is just a matter of putting the newly created measure in a table filter?

[DataInsights]

@kamiluc,

Here's a simpler solution.

1. Add Start and End columns to the Action Group table:

2. Measure:

Action Status (Baseline Date) = 
VAR vStart =
    SELECTEDVALUE ( 'Action Group'[Start] )
VAR vEnd =
    SELECTEDVALUE ( 'Action Group'[End] )
VAR vResult =
    COUNTROWS (
        FILTER (
            RPT_IAA_Findings_Actions,
            DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) >= vStart
                && DATEDIFF ( [Baseline Date], RPT_IAA_Findings_Actions[Due Date], DAY ) < vEnd
        )
    )
RETURN
    vResult

3. To cross-filter a table visual, create the following measure:

Visual Filter = 
VAR vGroupSelection =
    VALUES ( 'Action Group'[Group] )
VAR vActionAge = [Action age]
VAR vActionGroup =
    MAXX (
        FILTER (
            'Action Group',
            vActionAge >= 'Action Group'[Start]
                && vActionAge < 'Action Group'[End]
        ),
        'Action Group'[Group]
    )
VAR vTableWithGroup =
    ADDCOLUMNS ( RPT_IAA_Findings_Actions, "@ActionGroup", vActionGroup )
VAR vTableWithGroupSelection =
    FILTER ( vTableWithGroup, [@ActionGroup] IN vGroupSelection )
VAR vResult =
    IF ( NOT ISEMPTY ( vTableWithGroupSelection ), 1 )
RETURN
    vResult

4. In a table visual, create a visual filter using the measure [Visual Filter]:

Result: