Forum Discussion

Silko's avatar
Silko
Helper II
8 years ago
Solved

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 absen...
Need Help
Tips and Tricks
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
Silko
Helper II
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Silko

     

    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?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

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