cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative total between start date & end date

Hi all,

I am trying to figure out the following for a while already, unsuccessfully so far.

I would like to show 3 different colums with:

• Cumulative total project budget
• Project budget for period (F.e. Q1 = 3* €3,667 for project A)
• Cumulative realized project budget (F.e. Q1 2015: Project A = €33,333  - Project H = €5,780   -  Project I =  €4,000

Desired result:

I have the following data:

• Project nr
• Start date
• End date
• Project budget
• Duration
• Budget per month

I have already created a table calendar (as below) to try to use the 'order in progress' method, however I cannot figure it out.

Any recommendations how to solve this?

Thanks!

1 ACCEPTED SOLUTION
Community Support

HI @Mvl,

It probably required you to create some variables with summarize function to manually group records and calculate the aggregated results.

You can try to create a table visual with 'year quarter' field and the following measure formula if it works for your scenario:

``````Cumulative total project budget =
VAR _realRange =
CALCULATETABLE ( VALUES ( Calendar[Date] ), VALUES ( Calendar[YearQuarter] ) )
VAR _cumRange =
CALCULATETABLE (
VALUES ( Calendar[Date] ),
FILTER ( ALLSELECTED ( Calendar ), [Date] <= MAXX ( _realRange, [Date] ) )
)
VAR minDate =
MINX ( _cumRange, [Date] )
VAR maxDate =
MAXX ( _cumRange, [Date] )
VAR filtered =
FILTER (
ALLSELECTED ( Table ),
COUNTROWS ( INTERSECT ( _cumRange, CALENDAR ( [Start Date], [End Date] ) ) ) > 0
)
VAR summary =
filtered,
"Duration", DATEDIFF ( MAX ( minDate, [Start Date] ), MIN ( maxDate, [End Date] ), MONTH )
),
"Result", [Budget per Month] * [Duration]
)
RETURN
SUMX ( summary, [Result] )``````

BTW, I found your projects contain records that not start/end with month start/end with date category ranges. If you did not use month count to calculate with the budget, how did you deal with these not completed records?
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
5 REPLIES 5
Community Support

HI @Mvl,

It probably required you to create some variables with summarize function to manually group records and calculate the aggregated results.

You can try to create a table visual with 'year quarter' field and the following measure formula if it works for your scenario:

``````Cumulative total project budget =
VAR _realRange =
CALCULATETABLE ( VALUES ( Calendar[Date] ), VALUES ( Calendar[YearQuarter] ) )
VAR _cumRange =
CALCULATETABLE (
VALUES ( Calendar[Date] ),
FILTER ( ALLSELECTED ( Calendar ), [Date] <= MAXX ( _realRange, [Date] ) )
)
VAR minDate =
MINX ( _cumRange, [Date] )
VAR maxDate =
MAXX ( _cumRange, [Date] )
VAR filtered =
FILTER (
ALLSELECTED ( Table ),
COUNTROWS ( INTERSECT ( _cumRange, CALENDAR ( [Start Date], [End Date] ) ) ) > 0
)
VAR summary =
filtered,
"Duration", DATEDIFF ( MAX ( minDate, [Start Date] ), MIN ( maxDate, [End Date] ), MONTH )
),
"Result", [Budget per Month] * [Duration]
)
RETURN
SUMX ( summary, [Result] )``````

BTW, I found your projects contain records that not start/end with month start/end with date category ranges. If you did not use month count to calculate with the budget, how did you deal with these not completed records?
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Hi Xiaoxin,

First of all, thank you for your proposed solution. The code you provided is applicable for the project I am working on right now.
Instead of calculating cumulative budget quarterly, I want to plot the cumulative progress vs week for different company (see the table below).

I managed to plot the curve using your proposed code. However, instead of 5 curves (ie 5 companies), only one curve appears.

the graph will change as per company selected.

Any suggestions on how to solve this?

Thanks.

Frequent Visitor

@v-shex-msft Thanks a lot for your help!

This was exactly what I was looking for 🙂

Frequent Visitor

@amitchandak Thank you for the file. It definitely helped me in the right direction!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors