IF function with Custom Column: Dossier active on specific date( reference date)

[Anonymous]

Hello,

I tried to find some direction on de PB forum on this subject but i could not find it.

Question: I have  a table with dossiers, begindate and enddate.

I want to know if a dossier was active (active = no enddate or enddate after specific date)

The specific dates(reference date) are:

1-7-2016 

1-7-2019

Table Dossier:

Dossiernumber	Begindate	Endate
1	1-3-2016	15-7-2016
2	1-5-2015	2-6-2016
3	5-3-2018	1-8-2019
4	1-9-2016	1-8-2019
5	2-3-2016	5-7-2016
6	1-1-2019	1-9-2019
7	1-7-2016	5-5-2017
8	5-5-2017	2-2-2018
9	1-1-2019	31-12-2019
10	1-7-2016	1-7-2019

The answer in Excel is: 0 = NO 1 = YES

Dossiernumber	Begindate	Endate	1-7-2016	1-7-2019
1	1-3-2016	15-7-2016	1	0
2	1-5-2015	2-6-2016	0	0
3	5-3-2018	1-8-2019	0	1
4	1-9-2016	1-8-2019	0	1
5	2-3-2016	5-7-2016	1	0
6	1-1-2019	1-9-2019	0	1
7	1-7-2016	5-5-2017	1	0
8	5-5-2017	2-2-2018	0	0
9	1-1-2019	31-12-2019	0	1
10	1-7-2016	1-7-2019	1	1

[v-yuta-msft]

@Anonymous ,

So according to logic "active = no enddate or enddate after specific date", why the flag is 0 when EndDate is "2-6-2016"? Could you please clarify the logic of this requirement?

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft 

Thank you for your reply.
In my example i have written down all possible combinations. We are counting Dossiers for evaluating a possbile growth in dossiers over time. As reference date we are evaluating period 1-7-2016 against 1-7-2019. Which dossiers where active on reference date 1-7-2016? In excel i did it by hand. I put 0 at non active dossiers and 1 at actieve ones. The filter in excel showed 0 and 1 , no blanks. At this point i am certain i did not miss any combination.

If now The calculation is done ln power bi i can match my excel result against it. So for now it is great of you could help me with only the active results.

The dossiers with an end date before 1-7-2016 are then to be blank. The same for dossiers with an end date before 7-2019. no calculation needed for inactieve dossiers.

Answering your question:
The 0 at end date 2-6-2016 was a check mechanisme for The human eye in excel.

[Anonymous]

Thank you for your reply.
In my example i have written down all possible combinations. We are counting Dossiers for evaluating a possbile growth in dossiers over time. As reference date we are evaluating period 1-7-2016 against 1-7-2019. Which dossiers where active on reference date 1-7-2016? In excel i did it by hand. I put 0 at non active dossiers and 1 at actieve ones. The filter in excel showed 0 and 1 , no blanks. At this point i am certain i did not miss any combination.

If now The calculation is done ln power bi i can match my excel result against it. So for now it is great of you could help me with only the active results.

The dossiers with an end date before 1-7-2016 are then to be blank. The same for dossiers with an end date before 7-2019. no calculation needed for inactieve dossiers.

Answering your question:
The 0 at end date 2-6-2016 was a check mechanisme for The human eye in excel.