Determine if date is between 2 dates (example 2)

[Silko]

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

[Zubair_Muhammad]

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" )

[Silko]

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
...

[Zubair_Muhammad]

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

[Silko]

@Zubair_Muhammad yes that's correct.

[Zubair_Muhammad]

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] ) )
    )
)

[Zubair_Muhammad]

@Silko

[Silko · Accepted solution]

I managed to solve my problem with a solution given here:
http://community.powerbi.com/t5/Desktop/Dynamically-generate-table-rows-for-each-row/m-p/101483#M425...