Cumulative total between start date & end date

[Anonymous]

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!

[v-shex-msft · Accepted solution]

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

[MM_Laurence]

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]

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

This was exactly what I was looking for 🙂 

[amitchandak]

@Anonymous , Refer if this file, if it can help https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix?dl=0

[Anonymous]

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