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

[BDale93]

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

[some_bih · Accepted solution]

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!

[BDale93]

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.