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

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

desired result.JPG

 

I have the following data:

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

image.png

 

 

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

 

image.png

 

Any recommendations how to solve this?

 

Thanks!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
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.

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).

MM_Laurence_1-1675934760416.png

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

MM_Laurence_4-1675934879361.png

the graph will change as per company selected.

 

MM_Laurence_5-1675934930170.png

 

MM_Laurence_6-1675934955496.png

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 🙂 

 

 

amitchandak
Super User
Super User
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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