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

Need Help

5 Replies

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