Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
AKingma
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:

AKingma_0-1709114879825.png

 

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:

AKingma_1-1709114879827.png

 

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-202231-7-20231.511 16.5
1-1-202231-7-20222.56 15
1-8-202131-7-2022211 22
1-8-202131-7-2022011 0
1-8-202231-12-202214 4
1-8-202331-12-202314 4
1-8-202331-7-2024211 22
1-8-202331-7-2024111 11
      
1 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!

View solution in original post

3 REPLIES 3
v-zhengdxu-msft
Community Support
Community Support

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.

 

 

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!

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.

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors