Forum Discussion
IF function with Custom Column: Dossier active on specific date( reference date)
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 |
3 Replies
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
- 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. 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.
