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

[cjbaguley]

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 I'm trying to enhance to enable selecting a different point in time for comparison, however I can't seem to get the DAX right to consider a different date.

Example of the code that works:

VAR days_30 =

CALCULATE(

    COUNT('Lender Details'[LenderEmpNoText]),

    FILTER('Lender Details', 'Lender Details'[Lender Role Start Date] >= Today() - 30 && 'Lender Details'[Lender Role Start Date] <= Today() )

)

If I change out the Today function with another date (i.e. MIN(MasterDateTable[Date] or using another measure to calculate the MIN Date selected i nthe date slicer), I get a zero count:

VAR days_60 =

CALCULATE(

    COUNT('Lender Details'[LenderEmpNoText]),

    FILTER('Lender Details', 'Lender Details'[Lender Role Start Date] >= [SELECTED DATE] - 60 && 'Lender Details'[Lender Role Start Date] <= [SELECTED DATE] - 31)

)

I feel like the theory of what I'm trying to do is sound, but I'm guessing there's a difference in how Today is treated to how a calculated or MIN date selection works.

I've confirmed that the date Measure I created to try and account for this returns the correct date, and have also tried creating multiple measures for the vaiable to account for subtracting the days I want the variable to apply.

Essentially I'm trying to treat the date selected in the date slicer as "Today()". Have also checked for any other filters that may be impacting, and can't see any.

Any help appreciated as always.

Cheers 

[v-ssriganesh]

Hello @cjbaguley,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[cjbaguley]

Hi @v-ssriganesh,

Apologies for not replying sooner, long weekend in NSW last weekend and other things on this week, I have posted a reply to Owen and shared a copy of the pbix. Thanks!

[v-ssriganesh]

Hi @cjbaguley,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hi @cjbaguley,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @OwenAuger for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[OwenAuger]

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]

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]

@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]

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.

[OwenAuger · Accepted solution]

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?

[cjbaguley]

This nailed it!! Thank you so much @OwenAuger!!! Weeks of frustration sorted.

I still had a little diffculty when replicating in the real pbix, however once I realised that I had inadvertantly left the relationship between master date table and lender details blank in the sample and replicated in the real, it all fell into place!!! 

A second filter to ensure it was only lenders after the start date I wanted across the whole data source and I was off, and able to replicate your much more elegant measure for the other metrics being calculated off the basis of the lender start date.

Absolute star!!!!