Average days an event is open over the previous 12 months (this is rolling as the year progresses).

[DemoFour]

Hay everyone, 

I have a request to duplicate a visual the customer creates in excel into a report. 

Problem 

The customer would like a line chart with the Average Days an Event is Open on the Y axis and the month on the X axis - the month needs to advance as the year progresses.  (The chart today will show August 2023 to August 2024 next month it will show Sep 2023 - Sep 24) There will also be a secondary line with the metric of days that the Event should be Open (90 days). 



Data Model

There are in my mini model 3 tables Audit , Action and Date

Some sample data

Audit 

Audit ID	Date Raised	new_projectfunction	Audit Conducted Date	CAR Required
57	22/05/2024	100000032	22/05/2024	Yes
58	29/05/2024	100000040	02/05/2024	Yes
59	04/06/2024	100000004	09/05/2024	Yes
60	24/06/2024	100000032	11/06/2024	Yes
61	30/06/2024	100000032	30/06/2024	Yes
62	17/07/2024	100000040	17/07/2024	Yes
63	29/07/2024	100000032	29/07/2024	Yes
64	30/07/2024	100000032	16/06/2024	Yes
65	12/08/2024	100000040	12/08/2024	No

Actions

Audit ID	Audit Conducted Date	CAR Due Date	Completion Date	CAR Category	Standards Name	Standards Clauses	CAR Status	Days CAR Open
57	22/05/2024	22/08/2024	25/06/2024	Major	ISO 9001	7.5 Documented information	Closed	34
57	22/05/2024	22/08/2024	19/08/2024	Major	ISO 9001	7.5 Documented information	Closed	89
57	22/05/2024	29/06/2024	24/06/2024	Major	ISO 9001	7.5 Documented information	Closed	33
60	11/06/2024	15/09/2024	08/07/2024	SFI	ISO 9001	7.5 Documented information	Closed	27
61	30/06/2024	01/01/2025	30/06/2024	SFI	ISO 9001	9.2 Internal audit	Closed	0
62	17/07/2024	15/08/2024	21/08/2024	Major	ISO 45001	8.1 Operational planning and control	Closed	35
62	17/07/2024	15/08/2024	21/08/2024	Major	ISO 45001	8.1 Operational planning and control	Closed	35
58	02/05/2024	03/11/2024		Minor	ISO 14001	6.2 Environmental objectives and planning to achieve them	Open	113
59	09/05/2024	10/11/2024		Minor	ISO 14001	6.2 Environmental objectives and planning to achieve them	Open	106
60	11/06/2024	15/09/2024		SFI	ISO 45001	8.1 Operational planning and control	Open	73
63	29/07/2024	30/10/2024		Minor	ISO 9001	8.2 Requirements for products and services	Open	25
64	16/06/2024	26/09/2024		Minor	ISO 14001	9.1 Monitoring, measurement, analysis, and evaluation	Open	68

Date table is your standard date table built in M at a day per year = 365 with all the normal columns you need. 

There is One Audit to Many (N ) CARs attached to each audit.  

The date table is currently linked to the Audit Completed date but there are inactive relationships to Completion Date and Due Date but not being used in this instance. 

In the full model there are many dimensions for Staff and Projects etc., that the report is using. 

I can easily find the average days open for each Audit using AVERAGEX and iterating the total days by the ID

Ave days open CAR ID = 
AVERAGEX(
   VALUES( CAR[CAR ID] ),
    [Total Days CAR Open] 
)

 
I issue is that the visual is then totaling these by the conducted date and not giving the correct result as a rolling total each month, it is fixing them to the conducted date in the filter context from the date table. 

I think that what I need to do is build a table that takes the start date - then counts a running total of each day until it finishes - and then have these values in the line chart by the date as a moving total as time goes by with the dates for each date from the start date to the end dates.  I have built this in M to see what it looks like and as a virtual table but, I cannot make the leap to get the visual to match the above example.  - the customer manually builds this each month with raw totals and months calculated and then tunred into the graph.   

What the customer has done in Excel is easy as there placing each total value for the CAR in the table each month and producing the graph then changing the totals and the dates each month to make a new chart, but this is not how a semantic model works, this is the last piece of the puzzle and my brain hurts on a Friday sunny afternoon, so I think I am over thinking this and making it complicated! 

Any new thoughts would be a great help as my VAR virtual tables and DAX is getting longer and longer as I think about this problem and do not solve it!  

Any help greatly welcome. 

[Greg_Deckler]

@DemoFour I think you need something like this: Open Tickets - Microsoft Fabric Community

[DemoFour]

@Greg_Deckler 

This solution is counting the number of tickets in a time frame, what I need to do is average the number of days open on each day (on a rolling basis) so I am not sure it will work the same.

I need to work out the avarage days open on a given day for each event - so can this be used in the last VAR instead of a COUNTXX ? 

Output = 

Test = 

VARTmpTickets = ADDCOLUMNS(
    CAR , 
    "Effective Date" , 
    IF(
       ISBLANK( CAR[Completion Date] ),
        TODAY(),
        CAR[Completion Date]
    )
)
VARTmpTable = SELECTCOLUMNS(
    FILTER(
        GENERATE(
           TmpTickets,
            'Calendar'
        ),
        AND(
        [Date] >= CAR[Audit Conducted Date] && [Date] <= [Effective Date],
        NOT( CAR[Audit Conducted Date] = [Effective Date] )
        )
    ),
    CARID", CAR[CAR ID],
    "Date", [Date]
)

VARTmpTable1 = GROUPBY(
   TmpTable,CARID], 
    "Count",COUNTX(CURRENTGROUP(), [Date] )
)
RETURN

COUNTROWS(TmpTable1 )

[Greg_Deckler]

@DemoFour Correct, in your case you would need to modify the measure so that after you work out the effective date you would then need to add a column that calculated the number of days from the date opened to the effective date. You can use simple math to do this ( [effective date] - [opened date] ) * 1. for example. Once you have that, then you would need to do an AVERAGEX across the table for that column.

[DemoFour]

@Greg_Deckler  Can you post up an example please as I am not getting a cohent return value

[DemoFour]

Thanks @Greg_Deckler 
Will give that a go 🙂