Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DennisJung
Helper I
Helper I

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:

 

snip_20180219112159.png

 

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
v-ljerr-msft
Employee
Employee

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.

 

r2.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Employee
Employee

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.

 

r2.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

Hi, how would your solution for individual days?

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

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors