Forum Discussion

Larryten's avatar
Larryten
Frequent Visitor
1 year ago

Cumulative sum based on Report date and start date

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' ...
Need Help
rubayatyasmin's avatar
rubayatyasmin
Icon for Community Champion rankCommunity Champion
1 year ago

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's avatar
    Larryten
    Frequent Visitor
    1 year ago

    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's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion
      1 year ago

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

      • Larryten's avatar
        Larryten
        Frequent Visitor
        1 year ago

        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.