Filter a table with variables

[demaina]

Hello,

I have 1 table with [ID] [date] [availability] [status], this is an example of my dataset :

ID	Date	Availability	Status
1	09/10/2023	yes	Working
2	09/10/2023	yes	Learning
3	09/10/2023	yes	Working
4	09/10/2023	no	Off
1	10/10/2023	no	Off
2	10/10/2023	yes	Working
3	10/10/2023	yes	Worlking
4	10/10/2023	yes	Learning

I put 2 dates picker and my goal is to compare 2 dates in order to know who enter the company or who left the company between the two dates. I have 2 variables with the 2 dates chosen if it could help : vDate1="09/10/2023" and vDate2="10/10/2023"

The result should be in that case :

Newcomers (persons who were not available in Date1 and avaiable in Date2)

4	09/10/2023	no	Off
4	10/10/2023	yes	Learning

Outgoing (persons where ( (date=Date1 and Availability="yes") and (date=Date2 and Availability="no") ) )

1	09/10/2023	yes	Working
1	10/10/2023	no	Off

I don't manage to create this tables, my expressions don't work...

Could you please help me ?

[ajaykakkar93]

Hi,

Set analysis can be used to create a date range 

Example:
SUM({<[Term Date] = {">=01/01/2023<=03/31/2023"}>}[Profit USD])

TO

SUM({<[Term Date] = {">=$(vMinDate)<=$(vMaxDate)"}>}[Profit USD])

Where vMinDate & vMaxDate are variables what gives the output for the same

[demaina]

Thank you but I don't really understand how I can use it in my example... 

[ajaykakkar93]

Hi,

Lets assume the field [Term Date] has below dates

[Term Date] 

01/12/2022
02/12/2022
03/12/2022
04/12/2022
05/12/2022
etc...

steps

1. Create variables vMinDate with expression
   =date(min([Term Date] ))
2. Create variables vMaxDate with expression
   =date(max([Term Date] ))
3. Then in your measure add a set analysis
   =SUM({<[Term Date] = {">=$(vMinDate)<=$(vMaxDate)"}>}[Profit USD])

[demaina]

Hello ajaydkakkar, maybe my expectations weren't clear. 

 I want to have the list of ID that matches the following conditions : (Date=Date1 and Availability=No) and (Date=Date2 and Avaibability=Yes)

Do you have an idea on how to do that ? 

[ajaykakkar93]

Yes that can be done with thew same above method