Forum Discussion

Helper I

10 months ago

Solved

Calculation Filter working when using Today Function, but not when I use a calculated date

Hey Folks, I have a working measure that counts a number of users with a start date x number of days prior to today (<30,31 - 60, 61 - 90 etc). Works flawlessly for what I'm using it for, however...

Need Help

OwenAuger
10 months ago
Hi again cjbaguley

Thanks for sharing the PBIX!

I had a look and the measure itself actually seemed to be working correctly, however some visual interactions were disabled in the report page, which meant the card visual wasn't updating based on filters.

Nonetheless, I would recommend these other adjustments which I've made in the attached PBIX:

1. Added columns Offset Min and Offset Max to Lender Start Date Slicer:

2. Rewrote Lender Period Count to make use of these columns:

Lender Period Count = VAR SelectedDate = [Selected Date] VAR SingleFilter = HASONEFILTER ( 'Lender Start Date Slicer'[Days Since Start Date] ) VAR OffsetMin = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Min] ), -365 ) VAR OffsetMax = IF ( SingleFilter, SELECTEDVALUE ( 'Lender Start Date Slicer'[Offset Max] ), 0 ) RETURN CALCULATE ( COUNT ( 'Lender Details'[LenderEmpNoText] ), KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate + OffsetMin && 'Lender Details'[Lender Role Start Date] <= SelectedDate + OffsetMax ) ) + 0

The measure will apply a filter according to the single date range selected. If there are multiple or no selections made, the default offset is [-365,0].

3. I also added a visual-level filter to the table visual: Lender Period Count ≠ 0.

This seems to work as intended. Is this what you're looking for?
Sample PBI Lender Count.pbix5.7 MB

OwenAuger

Super User

10 months ago

Hi cjbaguley

The short explanation is that [SELECTED DATE] is being evaluated in the row context of 'Lender Details' iterated by FILTER. Each time it is evaluated, the "current row" of 'Lender Details' is converted to an equivalent filter which is applied when evaluating [SELECTED DATE] for that row.

However you want [SELECTED DATE] to be evaluated once and used within the Lender Role Start Date filter.

To fix this, I would recommend storing [SELECTED DATE] in a variable, and also applying filters on the Lender Role Start Date column within KEEPFILTERS rather than filtering the 'Lender Details' table. Generally it is best to filter columns rather than tables.

VAR days_60 =
VAR SelectedDate = [SELECTED DATE]
RETURN
    CALCULATE (
        COUNT ( 'Lender Details'[LenderEmpNoText] ),
        KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 60
            && 'Lender Details'[Lender Role Start Date] <= SelectedDate - 31 )
    )

Does an expression like this fix things?

Some relevant articles:

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

https://www.sqlbi.com/articles/context-transition-in-dax-explained-visually/

https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/

cjbaguley
Helper I
10 months ago
Thanks OwenAuger,

That really helped me understand the context of what I'm looking to do.

I've tried amending the whole measure using your methodology laid out, however I still seem to be running into issues. To add further context, the measure is using a table of (1-30 Days, 31-90days etc) which is in a slicer. If no slicer selection is made, it should return all values that are within 365 days before the selected date, otherwise apply the appropriate time banding for the slicer selection.

I have tried storing the 'selecteddate' variable both as each date banding, and also as a top line variable within the measure.

This is the full measure as I have it now, but its not returning any values whether filtered or unfiltered:

Lender Period Count =
VAR SelectedDate = [Selected Date]

VAR days_30 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 30
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate )
)

VAR days_60 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 60
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 31 )
)

VAR days_90 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 90
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 61 )
)

VAR days_120 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 120
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 91 )
)

VAR days_150 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 150
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 121 )
)

VAR days_180 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 180
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 151 )
)

VAR days_210 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 210
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 181 )
)

VAR days_240 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 240
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 211 )
)

VAR days_270 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 270
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 241 )
)

VAR days_300 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 300
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 271 )
)

VAR days_330 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 330
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 301 )
)

VAR days_365 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 365
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 331 )
)

VAR months_03 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 90
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate )
)

VAR months_36 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 180
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 91 )
)

VAR months_69 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 270
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 181 )
)

VAR months_912 =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 365
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate - 271 )
)

VAR days_all =

CALCULATE (
COUNT ( 'Lender Details'[LenderEmpNoText] ),
KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 365
&& 'Lender Details'[Lender Role Start Date] <= SelectedDate )
)

RETURN
IF(ISFILTERED('Lender Start Date Slicer'),
SWITCH(
MIN('Lender Start Date Slicer'[Days Since Start Date]),
"1 - 30 days" , days_30,
"31 - 60 days" , days_60,
"61 - 90 days" , days_90,
"91 - 120 days" , days_120,
"121 - 150 days" , days_150,
"151 - 180 days" , days_180,
"181 - 210 days" , days_210,
"211 - 240 days" , days_240,
"241 - 270 days" , days_270,
"271 - 300 days" , days_300,
"301 - 330 days" , days_330,
"331 - 365 days" , days_365,
"0 - 3 months" , months_03,
"3 - 6 months" , months_36,
"6 - 9 months" , months_69,
"9 - 12 months" , months_912),
days_all
) + 0
- OwenAuger
  Super User
  10 months ago
  cjbaguley
  
  I would try debugging each "sub-measure" by creating individual measures such as this
  
  days_30 measure = CALCULATE ( COUNT ( 'Lender Details'[LenderEmpNoText] ), KEEPFILTERS ( 'Lender Details'[Lender Role Start Date] >= SelectedDate - 30 && 'Lender Details'[Lender Role Start Date] <= SelectedDate ) )
  
  and testing in a simple visual.
  
  There is likely some combination of filters resulting in blank measures. Could you share a sample pbix?
  
  I would also suggest using a dynamic segmentation approach rather than repeating the code:
  https://www.daxpatterns.com/dynamic-segmentation/
  - cjbaguley
    Helper I
    10 months ago
    Thanks again Owen, apologies for the delay in reply, long weekend here and other priorities this week.
    
    I think I have been able to replicate my pbix properly without real data and uploaded ot my dropbox Sample Lender Details pbix
    
    I did try the debugging you suggested above, and while I could get it to work filtering anything >= Min(Date) (showing all 23 sample lenders, when I tried to do <= Min(Date) I get blanked out, when I'm expecting it to show only the 7 lenders who meet that criteria.

Forum Discussion

Calculation Filter working when using Today Function, but not when I use a calculated date

Related content

calculate with max() function

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation