Running total with filters

[Aymeric_bls]

Hello,

I'm currently trying to set-up a board where I could have an overview about the workloading of people per day.

I have a data table with the following inputs (the last column is a calculated column) :

TASK	PILOTE	STARTING_DATE	ENDING_DATE	WORKLOAD (in hour)	WORKLOAD_PER_DAY (Workload / (end-start))
1	a	19/04/2021	21/04/2021	6	6h / 3 = 2h per day
2	a	20/04/2021	22/04/2021	9	9h / 3 = 3h per day
3	b	19/04/2021	22/04/2021	8	8h / 4 = 2h per day
4	c	19/04/2021	20/04/2021	8	8h / 2 = 4h per day

I set up a calendar data base where I want to create a measure that will cumulate the daily workload of each tasks in order to have a visual chart and make sure the cumulated workload of each day is not more than x hours.

In this measure, I'd like to include a filter in order to vizualize this chart for each pilote.

In my mind, it looks like :

For each day of the Calendar table,

if STARTING_DATE <= Calendar[DATE] and ENDING_DATE > Calendar[DATE] and PILOTE = the one selected in the filters

then sum all the WORKLOAD_PER_DAY

It should return something like the following result :

Calendar data table for PILOTE = a :

DATE	Measure of Daily workload
19/04/2021  |	2 hours (task 1 only)
20/04/2021  |	5 hours (task 1 + task 2)
21/04/2021  |	5 hours (task 1 + task 2)
22/04/2021  |	3 hours (task 2 only)
23/04/2021  |	0 hours

Is there anybody who could guide me in this challenge ?

Thanks a lot for your help !

[Anonymous · Accepted solution]

[Daily Workload] =
// This measure only works on the individual
// date level. If anything more than one day
// is visible in the current context - blank
// will be returned.
var vDateSelected = SELECTEDVALUE( Calendar[Date] )
var vSumOfWorkloadPerDay =
    CALCULATE(
        SUM( T[Workload_Per_Day] ),
        // Note that you should not slice
        // by any columns in your fact table.
        // Please use dimensions instead.
        // The only exception to this rule is
        // a degenerate dimension (but you
        // don't seem to have it). Also, this code
        // assumes that Starting_Date and
        // Ending_Date can't be BLANK.
        KEEPFILTERS( T[Starting_Date] <= vDateSelected ),
        KEEPFILTERS( vDateSelected < T[Ending_Date] )
    )
RETURN
    vSumOfWorkloadPerDay

[Aymeric_bls]

Hello Daxer,

Your solution works perfectly, thanks a lot ! 😊