cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Running total of costs in combination with events in progress

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
1 ACCEPTED SOLUTION
Frequent Visitor

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.

3 REPLIES 3
Community Support

Hi @AKingma

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

How to provide sample data in the Power BI Forum

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.

Frequent Visitor

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.

Community Support

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors