Cumulative total based on week ending date

[marcritchie]

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 Timesheet End Date which is the applicable week ending date. This is connected to the full date in the date table - I then use a filter on the report which uses the Reporting Date > Week Ending as the filter.

In order to get a cumulative total for the hours based on the Week Ending that is selected by the user I have this measure:

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

The measure functions correctly in most cases but I have noticed strange behaviour when there is hours data in the future or beyond the final Week Ending date that is available for reporting. If that is the case it gives an incorrect figure on whatever Week Ending date I select and in the final Week Ending it includes data for the "future" dates.

I could filter out the original data but I would rather make it more robust by having the measure calculate correctly in these cases. Does anyone have any ideas?

[Greg_Deckler]

@marcritchie - You should be able to do this:

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

Otherwise, would need to see sample data and example of the issue you are describing. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[marcritchie]

Thanks @Greg_Deckler for the links, they are really useful and I'll use that format when I have to submit any other queries.

I found a workaround on this problem by filtering out all future dates on that date field in Transform Data.

[amitchandak]

@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]

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]

@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] )
)
)