cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Silko
Helper II
Helper II

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
7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

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

Please try my custom visuals


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

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

Regards
Zubair

Please try my custom visuals


@Zubair_Muhammad yes that's correct.

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

Please try my custom visuals


@Silko

 

1027.png

Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors