Running total of costs in combination with events in progress

[AKingma]

Hi all, I hope anyone can help me find the solution for my problem. Sorry for the amount of text needed to explain! Here is a picture of my model:

On the left, a dim-table with properties of schools, later on I want to be able to filter on name of school, area, etc. On the right the Calendar, to which I added “uurtarief_jr” and “uurtarief_mnd, being the costs for one hour of help, per year, respectively per month. These vary over time, i.e. in 2022 uurtarief_jr =  1265,- and in january 2023, it was 1325,-

In the center the facts-table, in which every row shows what help was given to a school, with a start date (Aanvang), end date (Einde), and number of hours per month. Thus, if a certain school obtained 3 hours of help in the months January, February, March, the total number of hours was 9. I have added a sample data table of the facts.

The result I want to get is the cumulative costs per year. I try to get there step by step with 3 measures: 1LopendArrAantal (Number of help trajectorys active at the filtered period of time), 2LopendArrUren (Number of hours of these), 3LopendArrKosten (Costs involved). Furthermore, I had an intermediate step/measure “Kostenpermaand”, which is the costs per month, but only in the first month of a change. Below I will give the code for all of these.

So far, this is the resulting visual:

As you can see, the totals do not show what I want. The total number of help trajectories is indeed 8, so measure 1 gives the correct total. The total of 11 hours under measure 2 however, is not very informative. That should be 94,5 (see sample data added below). More importantly, can anyone guide me to measure 4, which would give the running total? The running total should look like:

Year	Month	4CumulativeCosts
2021	8	211
2021	9	422
2021	10	633
2021	11	844
2021	12	1055
2022	1	474

Here is the code for my measures so far:

Number of help trajectorys active at the filtered period of time->

1LopendArrAantal =

VAR EndDatePerVisual = MAX('Calendar'[DateKey])

VAR StartDatePerVisual = Min('Calendar'[DateKey])

VAR RESULT =

CALCULATE(

    COUNTROWS(Arrangemententotaal),

    REMOVEFILTERS('Calendar'),

    Arrangemententotaal[Aanvang]    <=    EndDatePerVisual,

    Arrangemententotaal[Einde]   >             StartDatePerVisual

    ||

    ISBLANK( Arrangemententotaal[Einde] )

)

RETURN

RESULT

Number of hours (Urenpermaand means hours per month)

2LopendArrUren =

VAR EndDatePerVisual = MAX('Calendar'[DateKey])

VAR StartDatePerVisual = MIN('Calendar'[DateKey])

VAR RESULT =

CALCULATE(

    [Urenpermaand],

    REMOVEFILTERS('Calendar'),

    Arrangemententotaal[Aanvang]    <=    EndDatePerVisual,

    Arrangemententotaal[Einde]   >             StartDatePerVisual

    ||

    ISBLANK( Arrangemententotaal[Einde] ))

RETURN

RESULT

Wrong solution for the costs per month, resulting in the costs in the first month after a change:

Kostenpermaand =

SUMX(Arrangemententotaal, Arrangemententotaal[Urenpermaand] * RELATED('Calendar'[uurtarief_mnd]))

Better solution:

3LopendArrKosten =

VAR EndDatePerVisual = MAX('Calendar'[DateKey])

VAR StartDatePerVisual = MIN('Calendar'[DateKey])

VAR RESULT =

CALCULATE(

    [Kostenpermaand],

    REMOVEFILTERS('Calendar'),

    Arrangemententotaal[Aanvang]    <=    EndDatePerVisual,

    Arrangemententotaal[Einde]   >             StartDatePerVisual

    ||

    ISBLANK( Arrangemententotaal[Einde] )

)

RETURN

RESULT

Sample table of facts "Arrangemententotaal":

Aanvang (start date)	Einde (end date)	Aantal uren (number of hours)	LooptijdMND (Total number of months active		total nr of hours
1-8-2022	31-7-2023	1.5	11		16.5
1-1-2022	31-7-2022	2.5	6		15
1-8-2021	31-7-2022	2	11		22
1-8-2021	31-7-2022	0	11		0
1-8-2022	31-12-2022	1	4		4
1-8-2023	31-12-2023	1	4		4
1-8-2023	31-7-2024	2	11		22
1-8-2023	31-7-2024	1	11		11

[v-zhengdxu-msft]

Hi @AKingma 

What does the measure[Urenpermaand] or [Kostenpermaand] means?

Could you please share your data(without sensitive data), or create some sample data, so that we can help you better.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AKingma · Accepted solution]

Hi Zhengdong Xu,

I was having difficulties in uploading my sample.pbix and my message was marked as SPAM originally. Then I made a second effort, being more clear, but that was also marked as SPAM. I then went to another forum (enterpriseDNA), since I was in a hurry. Luckily, I got a very good solution over there. I don't know whether it is allowed to link another forum, but in case anyone is interested, here is the link to the solution: How to calculate running total from events in progress? - Power BI - Enterprise DNA Forum.

Thank you for your effort!

[v-zhengdxu-msft]

Hi @AKingma 

Congratulations!

It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards    

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.