Forum Discussion
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?
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#M4255
7 Replies
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" )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 | 12.1.2017 | 1
3.1.2017 | 1
3.1.2017 | 2
...My apologies. :smileyembarrassed:
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?
