Creating a DAX Measure for Dynamic Cumulative Total

[Imv_OrdixAG]

Hello fellow experts in Power BI and DAX,

I'm looking for assistance in creating a DAX measure that can effectively subtract the cumulative total of summed available worker capacity hours from their remaining order volume for a specific project. The cumulative total should be dynamically calculated from the current date to the user-selected end date, using a date slicer. The objective is to generate a forecast answering how workers will allocate their available capacity to fulfill the remaining order volume for the project.

To provide more context, I have a fact table with primary keys for each worker, project, date (daily granularity), a specific value type, and a numeric value column indicating hours.

Here are simplified examples of the data in Excel:

project_nr	worker_nr	date	value_type	value
1	2	01.04.2023	Total ordered hours	1200
2	1	01.01.2023	Total ordered hours	800
3	4	01.03.2023	Total ordered hours	1000

Example data for total order volume

project_nr	worker_nr	date	value_type	value
1	2	02.04.2023	Paid hours	8
1	2	03.04.2023	Paid hours	8
1	2	04.04.2023	Paid hours	8
1	2	05.04.2023	Paid hours	8
1	2	06.04.2023	Paid hours	8
1	2	07.04.2023	Paid hours	4
1	2	08.04.2023	Paid hours	8
1	2	09.04.2023	Paid hours	8
1	2	10.04.2023	Paid hours	6
1	2	11.04.2023	Paid hours	8

Example data for paid hours
To calculate the remaining order volume, I have a measure subtracting the total order volume from the accomplished paid hours (again simplified):

Remaining order volume =
IFERROR (
    [Total order volume]
        - CALCULATE (
            SUM ( 'f_hours'[value] ),
            'f_hours'[value_type] = "Paid hours",
            ALL ( d_date[date] )
        ),
    0
)
DAX-Measure for the remaining order volume

For available capacity, I use a simple measure with calculate to obtain the sum of the capacity (same as the paid hours calculate statement).

project_nr	worker_nr	date	value_type	value
null	2	01.09.2023	Available capacity	8
null	2	02.09.2023	Available capacity	8
null	2	03.09.2023	Available capacity	8
null	2	04.09.2023	Available capacity	8
null	2	05.09.2023	Available capacity	8
null	2	06.09.2023	Available capacity	8
null	2	07.09.2023	Available capacity	6
null	2	08.09.2023	Available capacity	8
null	2	09.09.2023	Available capacity	8
null	2	10.09.2023	Available capacity	8
null	2	11.09.2023	Available capacity	8

Example data for available capacity

Here's an example of the desired outcome:

Remaining order volume	Available capacity cumulative total	Date
600	8	01.09.2023
592	16	02.09.2023
584	24	03.09.2023
576	32	04.09.2023
568	40	05.09.2023
560	48	06.09.2023
554	54	07.09.2023
546	62	08.09.2023
538	70	09.09.2023
530	78	10.09.2023
522	86	11.09.2023

I initially explored using the TOTALYTD function, but it reset the remaining order volume each year if it wasn't fully accomplished by year-end:

Cumulative Subtraction Remaining Volume =
VAR StartDate =
    TODAY ()
VAR EndDate = [MaxDate]
RETURN
    IF (
        SELECTEDVALUE ( d_date[date] ) >= StartDate
            && SELECTEDVALUE ( d_date[date] ) <= EndDate
            && (
                [Remaining order volume]
                    - TOTALYTD (
                        [Capacity available],
                        d_date[date],
                        d_date[date] >= StartDate
                            && SELECTEDVALUE ( d_date[date] ) <= EndDate
                    )
            ) >= 0,
        [Remaining order volume]
            - TOTALYTD (
                [Capacity available],
                d_time[date],
                d_time[date] >= StartDate
                    && SELECTEDVALUE ( d_date[date] ) <= EndDate
            ),
        BLANK ()
    )
DAX-Measure as a try for the cumulative subtraction of the remaining order volume

Example of how the remaining order volume resets after the new year begins

What I need is a custom TOTALYTD function allowing me to specify a date range from the current date to the maximum date selected by the user. I've attempted examples from this forum like:

Cumulative total =
SUMX (
    FILTER (
        ALL ( d_date[date] ),
        d_date[date] >= TODAY ()
            && d_date[date] <= [MaxDate]
    ),
    [Capacity available]
)
Or the same measure but with CALCULATE instead of SUMX.

Unfortunately, these examples only sum the capacity for the filtered date range and don't fulfill my requirements. Any guidance or alternative approaches for the measure or the model of the fact table would be greatly appreciated. Also if you need any more data examples, feel free to ask. 

[Greg_Deckler]

@Imv_OrdixAG You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also, this: