Forum Discussion

marcritchie's avatar
marcritchie
Frequent Visitor
5 years ago

Cumulative total based on week ending date

I have a report that includes a Reporting Date table connected to a table which is updated weekly with data that shows hours that have been booked in a timesheet. The timesheet data includes a Times...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

marcritchie , In all such cases you should use the date or week table and the try with that. Also in case, you have a date use date. Grouping/Axis will take the case of weekend date

 

Actual Booked Cumulative =
CALCULATE (
SUM ( Replicon[Actual Booked Period] ),
FILTER (
ALL ( 'Date'[Date] ),
'Reporting Date'[Week Ending] <= MAX ( 'Date'[Date] )
)
)

  • marcritchie's avatar
    marcritchie
    Frequent Visitor
    5 years ago

    Thanks for the reply amitchandak 

     

    Just to clarify, in my solution the Date table is called Reporting Date.

    The fact table is called Replicon and includes the column "Timesheet End Date".

     

    I have a one to many relationship set from 'Reporting Date'[Date] to 'Replicon'[Timesheet End Date]

    If I understand correctly, in that case my formula should be:

    Actual Booked Cumulative =
    CALCULATE (
    SUM ( Replicon[Actual Booked Period] ),
    FILTER (
    ALL ( 'Reporting Date'[Date] ),
    'Replicon'[Timesheet End Date] <= MAX ( 'Reporting Date'[Date] )
    )
    )

     

    I think the problem I am having is there are multiple entries for "Timesheet End Date" as there are multiple jobIDs and DisciplineIDs for each date as per this screenshot so I can't use that formula:

     

    Do I have to incorporate the jobId and DisciplineID into the formula also?

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      marcritchie , Job id you need when you use the filter from this table and use all Replicon

       

      Create a dim for DisciplineID and a similar filter for it like date it should work

      FILTER (
      ALL ( 'DisciplineID'[DisciplineID] ),
      'DisciplineID'[DisciplineID] <= MAX ( 'DisciplineID'[DisciplineID] )
      )

       

      Other wise final option is

      Actual Booked Cumulative =
      CALCULATE (
      SUM ( Replicon[Actual Booked Period] ),
      FILTER (
      ALL ( 'RReplicon'),
      'Replicon'[Timesheet End Date] <= MAX ( 'Replicon'[Timesheet End Date] ) &&
      'Replicon'[jobId] = MAX ( 'Replicon'[jobId] ) &&
      'Replicon'[DisciplineID] <= MAX ( 'Replicon'[DisciplineID] )
      )
      )