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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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