Cumulative sum based on Report date and start date

[Larryten]

Hi All, 

I'm trying to obtain a cumulative sum based on a date range that will vary depending on when the database updates. I have 4 tables:

1) Planning Data - this will have a 'PlanningDataID' that is unique per project. A filter on all power BI pages will be used. 

2) Task - This has a column called 'ReportDate' and 'BaselineStart'

3) TaskTimephased - The cumulative information comes from a column called 'ActualDurationHours.' The Date info is from 'TimephaseEnd' and is linked to the planning data ID. 

4) Date - A date table made from Bravo that is linked to all date related columns in the other tables

I'd like to create a cumulative sum of 'ActualDurationHours' where the start date is the first date on 'BaselineStart' (From the task table) and the end date is last 'ReportDate.' (From the task table).  

The measure I'm using now is 

Project Functional Actual = CALCULATE(SUM('S Curve 01 Project Functionals'[ActualDurationHours]),FILTER(ALLSELECTED('S Curve 01 Project Functionals'),'S Curve 01 Project Functionals'[TimephaseEnd]<=MAX('S Curve 01 Project Functionals'[TimephaseEnd])))

however, after the report date it is a constant value which I don't want visible. 

Any help will be greatly appreciated. 

Kind Regards

[rubayatyasmin]

hi @Larryten 

tried a different approach. try this one below, 
Project Functional Actual =
VAR StartDate = MIN('Task'[BaselineStart])
VAR EndDate = MAX('Task'[ReportDate])
RETURN
CALCULATE(
SUM('S Curve 01 Project Functionals'[ActualDurationHours]),
FILTER(
ALLSELECTED('S Curve 01 Project Functionals'),
'S Curve 01 Project Functionals'[TimephaseEnd] >= StartDate &&
'S Curve 01 Project Functionals'[TimephaseEnd] <= EndDate
)
)

it should work, if it doesn't then share a demo dataset so that I can help. You can upload file in the file.io site and share the link. 

[Larryten]

hi @rubayatyasmin 

Appreciate the help

By using the above code, I'm not sure why, it only procudes the last cumulative value, so the graph is a constant value as opposed to a running cumulative value like what an S curve will show. With regards to demo dataset, the data is from a database which is locked would screenshots work?

I've got a new code that is as follows, 

'Trial Actual New =

VAR PlanningDate = MAX('PlanningData'[ReportDate])

VAR FirstSCurveDate = MIN('S Curve 01 Project Functionals'[TimephaseEnd])

VAR LastSCurveDate = MAX('S Curve 01 Project Functionals'[TimephaseEnd])

VAR HasValidDates = NOT(ISBLANK(PlanningDate)) && NOT(ISBLANK(LastSCurveDate))

RETURN

IF(

    HasValidDates,

    CALCULATE(

        SUM('S Curve 01 Project Functionals'[ActualDurationHours]),

        FILTER(

            ALLSELECTED('S Curve 01 Project Functionals'),

            AND(

                'S Curve 01 Project Functionals'[TimephaseEnd] < PlanningDate,

                'S Curve 01 Project Functionals'[TimephaseEnd] >= FirstSCurveDate

            )

        ),

        USERELATIONSHIP('S Curve 01 Project Functionals'[PlanningDataID], 'PlanningData'[PlanningDataID])  // Adjust relationship columns as needed

    ),

    BLANK()

)'

The problem I'm having is the values are inverted. the value at nov 24 is meant to be the value at august 24. I can't locate where the code inverts it

[rubayatyasmin]

Happy to help!! Can you share a demo file? Go to file.io and upload your demo and share the link. 

[Larryten]

Sure, please see link
https://file.io/5QSYXfzSqd3y  I've had a little play around and added some more info but the principle is still the same.

[v-linhuizh-msft]

Thanks for the reply from rubayatyasmin.

Hi @Larryten ,

Please try the following DAX:

Trial Actual New = 
VAR PlanningDate = MAX('PlanningData'[ReportDate])
VAR FirstSCurveDate = MIN('S Curve 01 Project Functionals'[TimephaseEnd])
VAR LastSCurveDate = MAX('S Curve 01 Project Functionals'[TimephaseEnd])
VAR HasValidDates = NOT(ISBLANK(PlanningDate)) && NOT(ISBLANK(LastSCurveDate))
RETURN
IF(SELECTEDVALUE('S Curve 01 Project Functionals'[TimephaseEnd])<=FirstSCurveDate&&SELECTEDVALUE('S Curve 01 Project Functionals'[TimephaseEnd])<PlanningDate,
IF(
    HasValidDates,
    CALCULATE(
        SUM('S Curve 01 Project Functionals'[ActualDurationHours]),
        FILTER(
            ALLSELECTED('S Curve 01 Project Functionals'),
            AND(
                'S Curve 01 Project Functionals'[TimephaseEnd] < PlanningDate,
                'S Curve 01 Project Functionals'[TimephaseEnd] <= FirstSCurveDate
            )
        ),
        USERELATIONSHIP('S Curve 01 Project Functionals'[PlanningDataID], 'PlanningData'[PlanningDataID])  // Adjust relationship columns as needed
    ),
    BLANK()
)
)

The result is as follows:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.