Create a graph showing a Field evolution over time

Hello

I'm beginning to better understand your scenarios: it's the Event in Progress pattern. You can do this with the original dataset (not the one you modified in Excel), without having to retrieve the weekly history.

I have worked on a solution with its original dataset and can find here the .pbix file.

I've used some dummy data:

6 tickets in total, 3 months in total, 2 tickets are collected each month.

At the end 2 are closed and 4 are still open

And I get the next visual (you could use a different one if you want)

Consideration of history

Do you need all the files for the history? I don't think you need it, as it has the High Date and Closed Date columns in the dataset that provide us with history.

Here we can:

• calculate for a specific period (one day, one month, or one year) the number of tickets collected, opened or closed during the period.
  These measures will help us check if our next measure returns correct values

• calculate the number of tickets currently collected or in progress (i.e. all tickets that are not yet closed)

Please note that because you do not have a date ticket in progress, we may over time calculate the number of tickets currently collected or in progress, but we cannot calculate over time the number of tickets currently in progress only

The main steps are:

1. Add a date table and connect it with your main table

2. Create the measure that returns the number of tickets raised/closed during the period

3. Create the final measure Currently raised or current entries to see evolution over time

Step 1: Create a date/calendar table

I used a standard DAX code:

Calendar = 
ADDCOLUMNS(
    CALENDAR(
        MIN(
            MIN( DataTickets[Date closed] ),
            MIN( DataTickets[Date raised] )
        ),
        MAX(
            MAX( DataTickets[Date closed] ),
            MAX( DataTickets[Date raised] )
        )
    ),
    "Month Number", MONTH([Date]),
    "Month", FORMAT([Date], "mmm"),
    "Year", YEAR([Date])
)

Next, you must connect the Calendar table to the main table:

• Calendar[Date] to MainTable[Date raised] as an active relationship
• Calendar[Date] to MainTable[Closed Date] as an inactive relationship

You should get the following:

Step 2: Create the measure to calculate what happened during a specific period:

Elevated entries in the period:we can take advantage of the active relationship and just write this

Raised tickets in period = COUNTROWS( DataTickets )

Closed entries in period: here we have to use the inactive relationship

Closed tickets in period = 
CALCULATE(
    COUNTROWS( DataTickets ),
    USERELATIONSHIP( 'Calendar'[Date] , DataTickets[Date closed] ),
    DataTickets[Date closed] <> BLANK()
)

These measures will not be used directly to the final measure, but will help us understand if we get the right result.

Step 3: Create the currently raised or current Entries measure

I used the following code:

Currently raised or in progress tickets = 

VAR LastDateOfPeriod = LASTDATE( 'Calendar'[Date] )

VAR Result =
    CALCULATE(
        COUNTROWS( DataTickets ),
        
// as we will manipulate the filter on calendar, first we remove any filter on this table
        REMOVEFILTERS( 'Calendar' ),

// we keep only tickets that have been raised during or before the last date displayed in the original filter context
        DataTickets[Date raised] <= LastDateOfPeriod,

// and that are closed after this same date (or not closed at all, i.e. blank)
        IF( -- if Date closed column is empty, we use a date in the future
            ISBLANK(DataTickets[Date closed]),
            DATEVALUE("9999-12-31"), 
            DataTickets[Date closed]
        ) > LastDateOfPeriod
    )
    
RETURN Result

Note that for this measure to work, you will need to cut and cut the visuals with the columns present in the Calendar table.

Full explanation of the current event pattern:

What I described above is very inspired by what sqlbi DAX masters describe in their "Event in Progress" pattern. You can find your great item here.

I hope it helps!

Best

Thomas