cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate Days between two dates - only for the current month

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Calculate Days between two dates - only for the current month

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

4 REPLIES 4
Super Contributor

## Re: Calculate Days between two dates - only for the current month

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

Regular Visitor

## Re: Calculate Days between two dates - only for the current month

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!

Highlighted
Frequent Visitor

## Re: Calculate Days between two dates - only for the current month

Hi, how would your solution for individual days?

Frequent Visitor

## Re: Calculate Days between two dates - only for the current month

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