Workload history pattern, but with several distinct statusses

[Artefact]

Hello,

The workload DAX pattern to calculate the workload (eg on helpdesk tickets) on every day in the past is clear. But I want the workload, specified also by the status of a ticket. The table with the source data for this shows the following columns, and sample data (european date format!) :
I want a visual which shows the number of tickets of each status which were open on a daily basis, with history. 

Ticket_id	Status	Date to this status	Date from this status
1	analyse	1-1-2025	1-2-2025
1	work	2-2-2025	2-3-2025
1	hold	3-3-2025	20-3-2025
2	intake	2-2-2025	4-2-2025
2	work	5-2-2025	23-2-2025

[v-vpabbu]

Hi @Artefact,

Thanks @Hakuna_matata  and @DataNinja777 for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Artefact,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Artefact,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Hakuna_matata]

Hi @Artefact ,

First lets create a date table 

DateTable = CALENDAR(MIN('Table'[Date to this status]), MAX('Table'[Date from this status]))

Than we can create a measure 

TicketsInStatusOnDate =

CALCULATE(

COUNTROWS('Table'),

'Table'[Date to this status] <= MAX('DateTable'[Date]) &&

'Table'[Date from this status] >= MIN('DateTable'[Date])

)

Than we can use any visual I have used stacked column chart

Hope it helps!
Please mark this as a solution if this help.

[DataNinja777]

Hi @Artefact ,

To show the number of tickets in each status on a daily basis using DAX, you can apply a pattern similar to headcount logic. Instead of expanding the dataset in Power Query to create one row per ticket per date, you can calculate whether a ticket was open on a given date using a measure. This approach keeps your data model lean and allows you to work with the original StatusLog table, which includes columns: Ticket_id, Status, Date from this status, and Date to this status.

First, create a calendar table that dynamically covers the date range of interest. This version expands from the earliest "from" date in the data up to today:

Calendar =
ADDCOLUMNS (
    CALENDAR (
        MIN ( 'StatusLog'[Date from this status] ),
        MAX ( MAX ( 'StatusLog'[Date to this status] ), TODAY () )
    ),
    "Date", [Date]
)

Make sure you create a relationship between Calendar[Date] and a separate date field in your visuals. Since the StatusLog table does not have a native date column for daily analysis, the relationship will be indirect—so the DAX measure will be responsible for enforcing the logic.

Now, create the measure to count the number of tickets open on each day per status. This checks whether the selected date in the context falls between the Date from this status and Date to this status:

Open Tickets = 
CALCULATE(
    DISTINCTCOUNT('StatusLog'[Ticket_id]),
    FILTER(
        ALL('StatusLog'),
        'StatusLog'[Date from this status] <= MAX('Calendar'[Date]) &&
        'StatusLog'[Date to this status] >= MAX('Calendar'[Date])
    )
)

To analyze by status, simply include StatusLog[Status] as a legend or column in your visual (e.g. a stacked area chart or matrix). This will break down the count of open tickets by their current status on each date.

This approach is flexible and efficient, avoiding unnecessary data expansion while still providing a detailed historical workload view by ticket status.

Best regards,