How to calculate running total from events in progress?

Power BI
1

Hi all,
I’ve been struggling with my data for a couple of weeks, found similar problems and got quite close to the solution, at least I think so, but I cannot find te final step. Any help would be appreciated!
See my included pbix for more info.

My events have varying StartDate and EndDate, and a different number of hours that repeat in every month between StartDate and EndDate. Furthermore, the “CostsHourMonth” change over time: these are the costs for one hour per month. So, an event that runs 3 months at a pricelevel of 30,- per hour and take 2 hours per month, would cost 180,- but if the pricelevel increases during the runtime of the event, this should be taken into account.

My required output is cumulative costs per year. My FactsTable included here, is representative but small… It contains the data of one school in which events take place. In real, there is a dimension table with several schools, thus I want to work with measures and not with adding columns, because the filtering may change from school to area, etc.

Here is the code of the measure that is closest to what I want to obtain (please find the rest of the code in the pbix-file):

3CostsActiveEvents = 
VAR EndDatePerVisual = MAX('Calendar'[DateKey])
VAR StartDatePerVisual = MIN('Calendar'[DateKey])
VAR RESULT = 
CALCULATE(
    [Costspermonth],
    REMOVEFILTERS('Calendar'),
    FactsTable[StartDate]    <=    EndDatePerVisual,
    FactsTable[EndDate]   >             StartDatePerVisual
    ||
    ISBLANK( FactsTable[EndDate] )
)
RETURN
RESULT

My problem: the costs per month are correct, but do not add up to a correct (informative) total and I do not know how to obtain a running total from these. Totals shown are only of unique values, the first month in which a change took place is taken into account, but all months that have the same value are not.

exampleRunningTotal.pbix (94.6 KB)

2

Create a new measure

Measure = 
SUMX (
    SUMMARIZECOLUMNS ( 'Calendar'[Year], 'Calendar'[Month] ),
    [3CostsActiveEvents]
)

Or

3CostsActiveEvents = 
SUMX (
    SUMMARIZECOLUMNS ( 'Calendar'[Year], 'Calendar'[Month] ),
    CALCULATE (
        VAR EndDatePerVisual =
            MAX ( 'Calendar'[DateKey] )
        VAR StartDatePerVisual =
            MIN ( 'Calendar'[DateKey] )
        VAR RESULT =
            CALCULATE (
                [Costspermonth],
                REMOVEFILTERS ( 'Calendar' ),
                FactsTable[StartDate] <= EndDatePerVisual,
                FactsTable[EndDate] > StartDatePerVisual
                    || ISBLANK ( FactsTable[EndDate] )
            )
        RETURN
            RESULT
    )
)
3

Thank you so much, VilmarSchi! This works exactly as I hoped for!