Calculate Days between two dates - only for the current month

[DennisJung]

Hello!

I need some input and ideas from you, because i try to find a way to calculate the following:

i have a table with the absence of our workers and a date table. I try to calculate the following:

- days of the absence per month

- if one absence goes into the next month, i should be calculated only till the last day of the month

- the rest goes into the next month

- 12 single Measures are ok (for each month?)

The Table looks like this:

I already tried a measure for january:

Januar = IF(Tabelle11[Ende ].[Month] = "January";DATEDIFF(Tabelle11[Start].[Date];Tabelle11[Ende ].[Date];DAY);DATEDIFF(Tabelle11[Start].[Date];ENDOFMONTH(Tabelle11[Start].[Date]);DAY))

But if a do this for january, the 10 Days into february are not calculated correctly.

[v-ljerr-msft · Accepted solution]

Hi @DennisJung,

Based on my test, you should be able to follow steps below to get the expected result in your scenario.

1. Add a YearMonth column in your Date table if you don't have it yet.

YearMonth = YEAR('Date'[Date])*100+MONTH('Date'[Date])

2. Use the formula below to create a new measure.

Measure = 
VAR firstDayOfMonth =
    MIN ( 'Date'[Date] )
VAR lastDayOfMonth =
    MAX ( 'Date'[Date] )
RETURN
    SUMX (
        Table1,
        VAR s =
            MAX ( Table1[Start], firstDayOfMonth )
        VAR e =
            MIN ( Table1[End], lastDayOfMonth )
        RETURN
            IF ( s < e, DATEDIFF ( s-1, e, DAY ) )
    )

3. Show Name column as Rows, YearMonth column as Columns, and the measure as Values on the Matrix visual.

Here is the sample pbix file for your reference. 

Regards

[datanoob]

Hi, how would your solution for individual days?

[datanoob]

Hi, would your solution work for calculations for days?  YearDay?

[DennisJung]

Wow, thank you very much! 

I found a solution yesterday  by myself, but yours is 10 x better 😄

My workaround was to create 3 calculated column for each month and 1 measure per month 😕

Thank you!