cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Silko Regular Visitor
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
Silko Regular Visitor
Regular Visitor

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

7 REPLIES 7
Super User III
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
Silko Regular Visitor
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
Super User III

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

@Silko

 

My apologies. Smiley Embarassed

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
Silko Regular Visitor
Regular Visitor

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

@Zubair_Muhammad yes that's correct.

Silko Regular Visitor
Regular Visitor

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

Super User III
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
Super User III

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

@Silko

 

1027.png

Try my new Power BI game Cross the River

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors