Hello:
Having trouble with a Running Total measure (same logic is being applied to a separate running count measure). I want to get the pattern right so I can use it in multiple places.
Goal: To show a matrix visual with Calendar Period across the columns and the Running Total $$$ of new customers and Running Total of # of new customer as the rows.
We also filter the matrix visual to show a limited number of periods (we want the users to focus on recent history) in two ways. We use the "Filters on all pages" feature to 1) filter in quarter end periods AND the last period (this last period might be a quarter end, might be a month) and 2) filter in only the last 3 years (using relative date filter).
The two Running calcs mentioned above each rely on a base calculation (one for $$$ and one for count) that themselves leverage CALCULATE and a FILTER statement that filters for 5 conditions (e.g. customer type, etc.) and also leverage three variables to define the date range of the "running" period - aka the "look back" period - which needs to vary because as mentioned the last month might be a quarter end, or it might be the first or second month of the quarter.
Here is the rolling count measure:
Rolling Customer Count =
VAR EndOfRange = MAX ( Period[Date] )
VAR MonthNum = MONTH(EOMONTH(MAX(Period[Date]),0))
VAR StartOfRange = EOMONTH(EndDateOfRange,
SWITCH(
MonthNum,
1,-7,
4,-7,
7,-7,
10,-7,
2,-8,
5,-8,
8,-8,
11,-8,
3,-9,
6, -9,
9, -9,
12,-9,
0
)
)
VAR Result =
CALCULATE (
[Customer Count],
DATESBETWEEN('_Table'[Date]], StartOfRange,EndOfRange),
// ALLSELECTED vs ALLEXCEPT?
ALLSELECTED('_Table')
//ALLEXCEPT('_Table', '_Table'[Date])
)
RETURN
Result
I have been experimenting (debugging DAX is seemingly more difficult than other tools) with using ALLSELECTED and ALLEXCEPT without getting to the correct solution.
With ALLSELECTED, the values agree with our database with ONE major exception: Because ALLSELECTED is impacted by the filters applied to the visual (via the page filter) the values generated in the first couple displayed periods aren't based on a full lookback and are therefore incorrect.
What I want to do is have these rolling measures look back across the full date range defined by the DATESBETWEEN function but the context of the applied UI filter is preventing that (that's my thinking at least).
If I try using ALLEXCEPT with the Date column, the results are improved....the "look back" is the full range. However, the numbers aren't exactly correct and I haven't been able to figure out why.
So my questions are:
1. Any way to resolve this?
2. Is there some interaction between ALLSELECTED and ALLEXCEPT with the filters that get applied to the "base" calculations that I am leveraging for the Rolling Calculations?
