cancel
Showing results for
Did you mean:
Regular Visitor

## Determine if date is between 2 dates (example 2)

@v-jiascu-msft your answer to this question helped me a lot but I still have one problem. My example is a little bit different. Instead of quarters I want to append a column with user id who was absent on specfied day:
date         | user id
1.1.2017   |   1
2.1.2017   |   1
3.1.2017   |   2
...

I have a table of users absence:
start_date   | end_date   |  user_id
1.1.2017       2.1.2017       1
3.1.2017       3.1.2017       2
...

Your example works perfect if there is only one user absent on one day. But muliple users can be absent on one day. So in that case I need the table to be something like (user 1 and 2 both absent on 2.1.2017):
date         | user id
1.1.2017   |   1
2.1.2017   |   1
2.1.2017   |   2
3.1.2017   |
...

Can you or anyone else help?

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

7 REPLIES 7
Super User III

## Re: Determine if date is between 2 dates (example 2)

HI @Silko

How about adding a Calculated Column in your Main Table stating if the User was PRESENT or ABSENT on that day

Then you can filter only those who were ABSENT

```Present or Absent =
VAR Check =
CALCULATE (
COUNTROWS ( AbsenceTable ),
FILTER (
AbsenceTable,
AbsenceTable[User ID] = MainTable[User ID]
&& AbsenceTable[Start Date] <= MainTable[Date]
&& AbsenceTable[End Date] >= MainTable[Date]
)
)
RETURN
IF ( Check >= 1, "Absent", "Present" )```
Try my new Power BI game Cross the River
Regular Visitor

## Re: Determine if date is between 2 dates (example 2)

I'm not sure you got my question right. I'm talking only about absent users and my absence table looks like this:
start_date   | end_date   |  user_id (absent_user_id)
1.1.2017       3.1.2017       1
3.1.2017       3.1.2017       2

I need it to look like this:
date         |  absent_user_id
1.1.2017   |   1

2.1.2017   |   1

3.1.2017   |   1

3.1.2017   |   2
...

Super User III

## Re: Determine if date is between 2 dates (example 2)

@Silko

My apologies.

I thought you have 2 Tables.
One with Dates and Users.

And other one ... the absence Table

So you want to TRANsform your Absence table into that FORMAT... Right?

Try my new Power BI game Cross the River
Regular Visitor

Regular Visitor

Super User III

## Re: Determine if date is between 2 dates (example 2)

Hi @Silko

Good to see this Power Query Solution

Here is a DAX solution

Go to Modelling Tab>>>New Table

```NEW TABLE =
GENERATE (
ALL ( AbsenceTable[User ID] ),
GENERATESERIES (
CALCULATETABLE ( VALUES ( AbsenceTable[Start Date] ) ),
CALCULATETABLE ( VALUES ( AbsenceTable[End Date] ) )
)
)```
Try my new Power BI game Cross the River
Super User III

## Re: Determine if date is between 2 dates (example 2)

@Silko

Try my new Power BI game Cross the River

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!