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!

4

Hi all,
I thought I had obtained the solution to my problem, but it turns out there are two failures in my calculation. I guess both problems would be solved with a more detailed calculation.
Any help would be appreciated!
See my included pbix for more info.
exampleRunningTotal080524.pbix (82.3 KB)

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 and 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. Please also have a look at the second page of the pbix, where I show the facts table.
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 =
SUMX (
SUMMARIZECOLUMNS ( ‘Calendar’[Year], ‘Calendar’[Month] ),
CALCULATE (
VAR EndDatePerVisual =
MAX ( ‘Calendar’[DateKey] )
VAR StartDatePerVisual =
MIN ( ‘Calendar’[DateKey] )
VAR RESULT =
CALCULATE (
[Costsstartmonth],
REMOVEFILTERS(‘Calendar’),
FactsTable[StartDate] <= EndDatePerVisual,
FactsTable[EndDate] > StartDatePerVisual
||
ISBLANK( FactsTable[EndDate] )
)
RETURN
RESULT
)
)

Failure 1: if the event starts in 2022, and ends in 2024, for every month the costs are calculated based on the level of 2022. This is quite obvious, since the calculation works with “Costsstartmonth”. However, it is not obvious to me how to take into account the actual price level of the current month?
Failure 2: although I do want to see costs per month, they should actually be calculated per day, to take into account that not every event starts at the first or ends at the last day of the month. Now the event starting at Feb 26, 2024 and ending at July 31, 2024 counts for 6 full months and likewise the event ending at Jan 17, counts for the whole month of January.