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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.