Forum Discussion

smikou's avatar
smikou
Regular Visitor
8 years ago

Please help: How to calculate the intersection duration between a month and a date range?

Hello everybody,

 

I'm super new to Ms Query and Power BI, haven't coded for almost 10 years and need your help:

 

I have a table with 4 columns:

- Hiring date (date)

- Quiting date (could be a date and could be blank if there's no quiting date)

- Month (integer)

- Year (integer)

 

I want to calculate a fifth column telling me for each row how much of that month of the year did the employee stay.

 

Here are a few examples : 

Example 1:

- Hiring date: 01/01/2018

- Quiting date: null

- Month: 3

- Year: 2018

- Part pf month: 100%

 

Example 2:

- Hiring date: 01/01/2016

- Quiting date: 02/01/2018

- Month: 3

- Year: 2018

- Part pf month: 0%

 

Example 3:

- Hiring date: 03/15/2017

- Quiting date: 02/01/2018

- Month: 3

- Year: 2017

- Part pf month: 50%

 

Many thanks!

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi@ smikou 

    After my research ,You can try to add a column  like below:

    Column =
VAR monthstart =
    DATE ( [ Year], [Month], 1 )
VAR monthend =
    EOMONTH ( monthstart, 0 )
VAR qd =
    IF ( ISBLANK ( [Quiting date] ), TODAY (), Table2[Quiting date] )
VAR days =
    DATEDIFF ( monthstart, monthend, DAY ) + 1
RETURN
    IF (
        Table2[Hiring date] >= monthend
            || qd <= monthstart,
        0,
        IF (
            monthend >= Table2[Hiring date]
                && monthend <= qd
                && monthstart <= Table2[Hiring date],
            DIVIDE ( DATEDIFF ( [Hiring date], monthend, DAY ), days ),
            IF (
                Table2[Hiring date] >= monthend
                    || qd <= monthstart,
                0,
                IF (
                    monthend >= Table2[Hiring date]
                        && monthend <= qd
                        && monthstart >= Table2[Hiring date],
                    1,
                    IF (
                        monthend >= qd
                            && monthstart <= qd
                            && monthstart >= Table2[Hiring date],
                        DIVIDE ( DATEDIFF ( monthstart, qd, DAY ), days ),
                        0
                    )
                )
            )
        )
    )

    Result:

     

    Best Regards,

    Lin