Reply
Highlighted
Regular Visitor
Posts: 34
Registered: ‎11-09-2015
Accepted Solution

Determine if date is between 2 dates (example 2)

[ Edited ]

@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?


Accepted Solutions
Regular Visitor
Posts: 34
Registered: ‎11-09-2015

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

[ Edited ]

All Replies
Super User
Posts: 2,838
Registered: ‎09-27-2017

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" )
Regular Visitor
Posts: 34
Registered: ‎11-09-2015

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
Posts: 2,838
Registered: ‎09-27-2017

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?

Regular Visitor
Posts: 34
Registered: ‎11-09-2015

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

@Zubair_Muhammad yes that's correct.

Regular Visitor
Posts: 34
Registered: ‎11-09-2015

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

[ Edited ]
Super User
Posts: 2,838
Registered: ‎09-27-2017

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] ) )
    )
)
Super User
Posts: 2,838
Registered: ‎09-27-2017

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

@Silko

 

1027.png