Filtering a visual to display data for dates prior to min date of date range filter

[Usefulinfo]

Hi All,

Need your help to resolve below query.

I have a date range filter visual and a table visual whcih shows app details (app_name, received date etc). I want to show app count along with app details for only thoes app which has received_date < min (date). 

The below measure is giving correct result if I am adding REMOVEFILTERS('DateTable') but in this case the table visual shows all the received date irrespective of date filter visual bcz of REMOVEFILTERS which I dont want.

If I delete this line from measure then it give blank result bcz of date range filter, it couldn't find any received date less than min date of filter.

ShowApplication =

VAR MinFilterDate =

    CALCULATE(

        MIN('Date Table'[Date]),

        ALLSELECTED('Date Table')

    )

RETURN

CALCULATE(

    COUNTROWS('App'),

    REMOVEFILTERS('DateTable'),

    'App'[Received_Date] < StartDate

I have tried multiple approach but coldn't resolved this. Could you please guide me to achieve this.

[rajendraongole1 · Accepted solution]

Hi @Usefulinfo - To achieve this, you can avoid using REMOVEFILTERS by focusing on maintaining the date context from the filter visual. Instead of removing filters, we can use ALLSELECTED with a condition to include only dates earlier than the minimum selected date in the filter

ShowApplication =
VAR MinFilterDate =
CALCULATE(
MIN('Date Table'[Date]),
ALLSELECTED('Date Table')
)
RETURN
CALCULATE(
COUNTROWS('App'),
KEEPFILTERS('App'[Received_Date] < MinFilterDate)
)

hope this gives you correct app count with date filter. please check

[vojtechsima · Accepted solution]

@Usefulinfo since you use ReceivedDate as Key column for Calendar relationship, modify your solution to this:

CALCULATE(
    COUNTROWS('Sheet2'),
    DateTable[Date] < StartDate, -- the change is here, using Date column from calendar instead RecievedDate
    -- and also here, since it's different table you have to add these filter as new parameter, so split by comma
    ISBLANK('Sheet2'[Despatch_Date]) &&
    'Sheet2'[Status] <> "close"
    )

[Usefulinfo]

@vojtechsima ,Thank you for your reply.

I have calender table and another table app. There is many to one relationship b/w datetable[date] and app [received_date]. I need to show app details (name, received_date, valid date etc) for only thoes app which has received date < min(datetable[date]) in table visual.

This min(datetable[date]) should be calculated from date range filter 

[vojtechsima]

@Usefulinfo 
I am trying to recreate your setup:

and it works just fine, make sure you add the Measure to the table visual.

Perhaps, you can share some sample .pbix and I can work directly on that one.

[Usefulinfo]

@vojtechsima , I have created sample file but seems like dont have permission to attcahed the pbix file. 

Table visual 'Without_Keepfilter' giving correct result but received date is showing all date(not lesser date).

OpenApplicationBeforeFilterDate withoutfilter =

VAR StartDate =

    CALCULATE(

        MIN('DateTable'[Date]),

        ALLSELECTED('DateTable')

    )

RETURN

CALCULATE(

    COUNTROWS('Sheet2'),

     'Sheet2'[Received_Date] < StartDate &&

    ISBLANK('Sheet2'[Despatch_Date]) &&

    'Sheet2'[Status] <> "close" )

Table visual WithKeepfilter show blank for condition 'Sheet2'[Received_Date] < StartDate.

OpenApplicationBeforeFilterDate =

VAR StartDate =

    CALCULATE(

        MIN('DateTable'[Date]),

        ALLSELECTED('DateTable')

    )

RETURN

CALCULATE(

    COUNTROWS('Sheet2'),

    KEEPFILTERS( 'Sheet2'[Received_Date] < StartDate &&

    ISBLANK('Sheet2'[Despatch_Date]) &&

    'Sheet2'[Status] <> "close" ))

 -------------

I couldn't keep additional condition along with the solution you have suggested.

[vojtechsima · Accepted solution]

@Usefulinfo since you use ReceivedDate as Key column for Calendar relationship, modify your solution to this:

CALCULATE(
    COUNTROWS('Sheet2'),
    DateTable[Date] < StartDate, -- the change is here, using Date column from calendar instead RecievedDate
    -- and also here, since it's different table you have to add these filter as new parameter, so split by comma
    ISBLANK('Sheet2'[Despatch_Date]) &&
    'Sheet2'[Status] <> "close"
    )

[Usefulinfo]

@vojtechsima , Thank you so much for your suggesion. 

I did same but didn't get same result. It really look weired. 

I will check once again if I am missing something. 

[vojtechsima]

@Usefulinfo 
I am sending you my file in case:
smallerThanReceivedDateMinDate.pbix

make sure you have correct relationship, datatable is setup as calendar, you have full year of the calendar and you use calendar as the Slicer, those are general rules.

[Usefulinfo · Accepted solution]

Yes, I have all these details. I have tested again and its working fine. 

Thank you so much for your help.

[vojtechsima]

@Usefulinfo 
Thank you, feel free to mark the right answer as solution and I wouldn't mind kudos. thx

[Usefulinfo]

@rajendraongole1 , still geting blank result 😞 

[vojtechsima]

Hello, @Usefulinfo ,

if you have calendar (if you don't I suggest you create one), simple one-liner should work just fine.

display = 
CALCULATE(
    COUNTROWS('Table'),
    'Calendar'[Date] <= MIN('Calendar'[Date])
)

[Usefulinfo]

@rajendraongole1 , thank you for your reply. I tried this as well but it give blank result for condition 'App'[Received_Date] < MinFilterDate. 

Its showing correct result for greater condition.

 'App'[Received_Date] > MinFilterDate. 

Could you please suggest to fix this for 'App'[Received_Date] < MinFilterDate.  as well?

[rajendraongole1]

Hi @Usefulinfo - To troubleshoot and ensure that only records with Received_Date less than MinFilterDate are counted. slight update on previous measure. please check below one. using ALLSELECTED

ShowApplication =
VAR MinFilterDate =
CALCULATE(
MIN('Date Table'[Date]),
ALLSELECTED('Date Table')
)
RETURN
CALCULATE(
COUNTROWS('App'),
FILTER(
ALLSELECTED('App'),
'App'[Received_Date] < MinFilterDate
)
)

[rajendraongole1 · Accepted solution]

Hi @Usefulinfo - To achieve this, you can avoid using REMOVEFILTERS by focusing on maintaining the date context from the filter visual. Instead of removing filters, we can use ALLSELECTED with a condition to include only dates earlier than the minimum selected date in the filter

ShowApplication =
VAR MinFilterDate =
CALCULATE(
MIN('Date Table'[Date]),
ALLSELECTED('Date Table')
)
RETURN
CALCULATE(
COUNTROWS('App'),
KEEPFILTERS('App'[Received_Date] < MinFilterDate)
)

hope this gives you correct app count with date filter. please check

[Usefulinfo]

@rajendraongole1 , Thank you. This works too