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