Cumulative / Rolling Total with ALLSELECTED Limited to Calendar Table Range

[dashender7]

Hi, 

I've found several related topics but none that deal with the exact situation I'm trying to navigate.

I have a sales fact table and then a BoardBudget table to compare actual to projected sales quantities on a rolling / cumulative basis.  The requirement is to be able to "slice" by month to any period so that the cumulative totals for both the monthly BoardBudget projected numbers and that actual sales totals "start" at the beginning of the sliced period and are limited to that sliced period in a table or line over line graph.

 I have my Calendar table generating based on the range of dates in my sales fact table, which, using the following measure, effectively should limit the range of BoardBudget numbers to bring in based on dates. (Since I want to connect to a BoardBudget table with a wider range of dates in the past and future than my sales fact table). 

What I want to do is use the a measure so that the "visible" context of the BoardBudget table is limited to the range of dates in my calendar table.  I have about 5 different workarounds for this, including generating a calculated table for BoardBudget and relating to that, but none of them are satisfying to me. This is what I'm tring now, and I feel that I'm not understanding the ALLSELECTED fully:

Rolling Budget 2 =

CALCULATE(
      SUM( 'BoardBudget'[BudgetSalesNonRolling] ),
      FILTER(
              ALLSELECTED( 'Calendar'[Date] ),
                      'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] >= MIN( 'Calendar'[Date] )             
      ) 
)

Using the above measure causes the BoardBudget total to show a non-cumulative total instead of a rolling / cumulative total.

If I use the following measure, I can effectively do exactly what I'm wanting and everything works as I'd expect, getting exactly the rolling total I expect for whatever period with the start date I specify for the minum Calendar date:

Rolling Budget 2 =
CALCULATE(
      SUM( 'BoardBudget'[BudgetSalesNonRolling] ),
      FILTER(
              ALLSELECTED( 'Calendar'[Date] ),
                      'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] >= DATE(2015, 1, 1)            
      ) 
)

The only difference is I specified the MIN date explicitly instead of using MIN( 'Calendar'[Date]. The problem is that I don't want to have to specify the "MIN" date the way I am in the above measure.  I don't like having to update measures like this if we decide to bring in fact sales prior to that minimum date.  I want the measure to handle it correctly so that I will only be comparing BoardBudget numbers for the periods in which sales fact data is available - the same way my calendar table will generate based on the sales fact dates.  

I don't understand why using MIN( 'Calendar'[Date] ) doesn't work the same way as specifying the exact date (2015, 1, 1) which is in fact that minum date in my calendar table.  Any ideas?

Thanks!

[Anonymous]

Does this help? it helped me:  - https://youtu.be/RVcJ3O_O2Jg

process is explained at 1:22 or if you are impatient formula finished at 2:39 🙂 hope this helps.

[v-yuezhe-msft]

Hi @dashender7,

How about using the following formula to calculate Rolling Budget 2?

Firstly create a measure to calculate minimum date in sales fact table, for example, minmeasure= MIN(Sales[DateKey]), then change the formula of Rolling Budget 2 as follows.

Rolling Budget 2 =

CALCULATE(
      SUM( 'BoardBudget'[BudgetSalesNonRolling] ),
      FILTER(
              ALLSELECTED( 'Calendar'[Date] ),
                      'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] >= [minmeasure]          
      ) 
)  

In addition, to understand ALLSELECTED function, you can change ALLSELECTED function to ALL function, then check the difference between the two formulas. The difference is that when you create slicers using columns of Calendar table(for example, year column or month column) and use the slicers to filter visuals, the measure using ALLSELECTED function will be filtered, however the measure using ALL function will not be filtered. ALL function ignores all slicers and Visual/Page/Report filters, ALLSELECTED is removing only filters created by the rows/columns in the current query, but retains explicit filters.  For more details about difference between ALL() and ALLSELECTED(), please review this similar blog.

If the above steps don't help, please post sample data of your tables and post expected result here.

Thanks,
Lydia Zhang

[Anonymous]

I have tried this and it doesn't work. I have create measure with MIN sales date as you instructed, and used it in rolling total measure but it doesn't start calculationg from selected calendar min date.

[Brianoreilly]

Hi @Anonymous, 

Did you solve this?

I currently have the exact same issue. 

I want to use a calendar slicer with a min and max date and calculate a running total based on this. 

No answer anywhere. 

[Anonymous]

Sorry, I do not have solution for this.

[Brianoreilly]

Hi @Anonymous, 

Thanks for trying !

Appreciated.

[Sumdib]

Any luck on this? 

I'm facing the same issue - using a cumulative measure which I don't want the date slicer interferring with.

Utilizing the ALL function sorts this but then creates a situation where no other slicers correctly impact the cumulative count measure and the measure cannot be "toggled" by slicer either.

bc test = CALCULATE (
    COUNTA ( 'Mechanical Tag Status Table'[PTR - Status Table.MS Name]),
    FILTER (
        ALLSELECTED(  'Mechanical Tag Status Table'),
        'Mechanical Tag Status Table'[PTR - Status Table.Date] <=  MAX('Calendar'[Date])
    ),
'Mechanical Tag Status Table'[PTR - Status Table.MS Name] = "BID CLOSE", 'Mechanical Tag Status Table'[PTR - Status Table.MS Status] = "actual" || 'Mechanical Tag Status Table'[PTR - Status Table.MS Status] = "forecasted" || 'Mechanical Tag Status Table'[PTR - Status Table.MS Status] = "projected")

Edit: May have stumbled onto a solution: Use ALLSELECTED and disable or make inactive the relationship between the date table and the fact table.