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
BDale93
Helper I
Helper I

Cumulative totals (Not effected by date slicer) beside totals that are effected by date slicer

Hello all,

I have a request here to have fincial information side by side in a matrix. Revenue (Which is total revenue by category based on date slicer selection), and Rolling Total Revenue which is also by category but not based on date. I've created a copy of my main date table and not created any relationships to it and created the below measure. Unfortunately this is just giving me blanks regardless of date selections. Any assistance here would be appreciated.

Revenue: //Working as expected

SUM(time_card_by_day_with_cost[Total Revenue]

Rolling Revenue: //Receiving blanks for each line item, Max Filter measure is referencing the date from the copied date table
IF(HASONEVALUE('Date Table'[Month]),
IF(VALUES('Date Table'[Month]) < [Max Filter], [Total Revenue], BLANK()),BLANK())
1 ACCEPTED SOLUTION
some_bih
Super User
Super User

Hi @BDale93 possible solution as following: 

If you do not have Date, Calendar Table please create one and connect it with your table (time_card_by_day_with_cost). Check link below if you need instruction for creation of Date table.

In example below 'Date'[Date] column is key colum for conection your table and Date is name of Date table, so adjust name accordingly.

After that create measure 

Cumulative Total Test=
VAR __max_date = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUM(time_card_by_day_with_cost[Total Revenue]) , -- simple sum or your measure [Revenue]
'Date'[Date] <= __max_date,
ALL ( Date )
)

 

Create Calendar Table Microsoft Learn

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

 

Did I answer your question? Kudos appreciated / accept solution!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

2 REPLIES 2
some_bih
Super User
Super User

Hi @BDale93 possible solution as following: 

If you do not have Date, Calendar Table please create one and connect it with your table (time_card_by_day_with_cost). Check link below if you need instruction for creation of Date table.

In example below 'Date'[Date] column is key colum for conection your table and Date is name of Date table, so adjust name accordingly.

After that create measure 

Cumulative Total Test=
VAR __max_date = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUM(time_card_by_day_with_cost[Total Revenue]) , -- simple sum or your measure [Revenue]
'Date'[Date] <= __max_date,
ALL ( Date )
)

 

Create Calendar Table Microsoft Learn

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

 

Did I answer your question? Kudos appreciated / accept solution!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Thanks some_bih (lol),

This isnt exactly what I was looking for but I think suffices for the ask. For example, with this measure if I have October 2023 selected, Revenue for the month calculates correctly and Total Revenue does as well. However, if I switch to September 2023 Revenue for the month calculates correctly and Total Revenue calculates through September 2023 as well, where I was trying to get that to remain as totaling through October 2023 (The max date in the model). This is why I was trying to use a second date table for the total columns. Regardless, this should do and thanks for the assistance.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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