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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 @Anonymous,

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 =
ADDCOLUMNS (
ADDCOLUMNS (
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 @Anonymous,

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 =
ADDCOLUMNS (
ADDCOLUMNS (
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.

Anonymous
Not applicable

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

This was exactly what I was looking for 🙂

Super User
Anonymous
Not applicable

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

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors