Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
marcritchie
Frequent Visitor

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

5 REPLIES 5
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Super User
Super User

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

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:

Screenshot 2020-09-07 at 15.10.54.png

 

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

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.