Forum Discussion

DennisJung's avatar
DennisJung
Icon for Helper I rankHelper I
8 years ago
Solved

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.

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

     

    Regards

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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. :smileyhappy:

     

    Regards

    • DennisJung's avatar
      DennisJung
      Icon for Helper I rankHelper I
      8 years ago

      Wow, thank you very much! 

       

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

       

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

       

      Thank you!

    • datanoob's avatar
      datanoob
      New Member
      7 years ago

      Hi, how would your solution for individual days?

    • datanoob's avatar
      datanoob
      New Member
      7 years ago

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