Filter active records at a specific date

[Anonymous]

 Hello!

So the problem is: I have a classic dataset with datewarehouse historization; each record has a validFrom, validTo and an isActive flag.

I want to see the records in a table, that was active at the selected date, so logically: I select a date on a slicer. The list has to contain all the records, where is true, that the selected date is greater than the record's validFrom and smaller than the validTo. 

Is it a solveable problem in Power BI? If yes, can you tell ma how is this possible?

Thanks,

Matt

[rajulshah]

Hello @Anonymous,

Using the following DAX function, we can achieve what you want:

IsActive = IF(FIRSTDATE(ALLSELECTED('DTdates'[Date]))<=FIRSTDATE(Table1[ValidFrom]) && LASTDATE(ALLSELECTED('DTdates'[Date]))>=LASTDATE(Table1[ValidTo]) ,1,0)

Here is the required result:

Let me know if I am missing on anything.

[Anonymous]

@rajulshah

Hello Rayulshah!

I'm really appreciate your answer! It seems, it would be perfect solution, however FIRSTDATE and LASTDATE functions are not supported in Direct Query mode.

Is there any possible workaround for this kind of problem?

Thanks,

Matt

[rajulshah]

Hello @Anonymous,

Can you please try the following DAX formula and see if it works?

IsActive = IF(FIRSTNONBLANK(ALLSELECTED('DTdates'[Date]),'DTdates'[Date])<=FIRSTNONBLANK(Table1[ValidFrom],Table1[ValidFrom]) && LASTNONBLANK(ALLSELECTED('DTdates'[Date]),'DTdates'[Date])>=LASTNONBLANK(Table1[ValidTo],Table1[ValidTo]) ,1,0)

Let me know if it didn't work.

[rajulshah]

Hello @Anonymous,

Let me know if this formula worked or not.

Regards.

[Anonymous]

@rajulshah

Hello!

Unfortunatley it does not work.

I created a sample dataset, under this link. This is used in Azure SQL db, with DirectQuery.

Hope this is help.

Thanks,

Matt

[rajulshah]

Hello @Anonymous,

Can you please share which datasource are you using for DirectQuery?

If I may be of some help.

[v-yuezhe-msft]

@Anonymous,

Do you connect to SQL Server using Direct Query mode? Could you please post the error message?

Regards,
Lydia

[v-yuezhe-msft]

@Anonymous,

1. Create a date table in Power BI Desktop following the guide in this blog. Please note that there is no relationship between the date table and your original table.

2. Create a measure using DAX below in your original table.

chkmeasure = IF(MAX(Table[vaildFrom])<=MAX('Date'[Date])&&MAX('Date'[Date])<=MAX(Table[validTo]),1,0)

3. Create a table visual as shown in the following screenshot, create slicer using date field in the Date table, drag the chkmeasure to visual level filter, and set its value to 1. And choose yes for isActive field.



Regards,
Lydia

[Anonymous]

@v-yuezhe-msft,

Hey Lydia,

Thanks for your answer! However, this is not a correct way, to solve my problem, because with this solution, we can find those records, which was already active at the selected date, and STILL currently active records.

The problem is, that I cant find those records, which not active anymore, but was active at a specific date in the past.

When I select a specific date, I want all the records, which was active at that day (SELECTED DATE was between validTo and validFrom[For these records, the validity can be 0 [not valid anymore]]), and thoose ones, which still actives since then (SELECTED DATE was between validTo and validFrom[For these records, the validity is 1 [still valid]]).

Do you see any way to solve this problem?

Thanks,

Matt