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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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!:
Power BI Cookbook Third Edition (Color)

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.