locked
PowerPivot DAX: Using project start and end date, Calculate Number of days per Month of an individual person. RRS feed

  • Question

  • Hi

    I am having a spread sheet ‘booking’, which records the Person name, start and end date of the person on the project schedule. I also have another spread sheet ‘Dates’ with one of its column as date.

    Requirement: I need to have power pivot solution where I need to see employee names in rows and months in columns and the detail data (potentially calculated measure using DAX) should be the numbers of days in each month. 

    Booking

    Person

    start

    end

    ABC

    1-Jan-11

    3-Feb-11

    EFG

    15-Jan-11

    2-Feb-11

     

    Dates

    Date

    Year

    Month

    WeekDay

    1-Jan-08

    2008

    Jan

    Tue

    2-Jan-08

    2008

    Jan

    Wed

    3-Jan-08

    2008

    Jan

    Thu

    4-Jan-08

    2008

    Jan

    Fri

    5-Jan-08

    2008

    Jan

    Sat

    6-Jan-08

    2008

    Jan

    Sun

    7-Jan-08

    2008

    Jan

    Mon

     

    Desired Output

    Measure 1

    Column Labels

     

    Row Labels

    Jan

    Feb

    Grand Total

    ABC

    29

    3

    32

     

    EFG

    14

    2

    16

     

    Grand Total

    43

    5

    48

     

     

    So far I have wrote DAX to create a calculate measure in Pivot Fields and using it as Pivot values, which gives the total number of days but does not split days total per months.

     

    DAX:

    =Calculate( sum(booking[days]),

                            FILTER(booking,

                                                  COUNTROWS(FILTER(VALUES(Dates[Date]),

                                                                            booking[start] <= Dates[Date] &&

                                                                            booking[end] >= Dates[Date] ))

                                                    > 0)

                            )

    DAX current output

    Measure 1

    Column Labels

     

    Row Labels

    Jan

    Feb

    Grand Total

    ABC

    32

    32

    32

     

    EFG

    16

    16

    16

     

    Grand Total

    48

    48

    48

     

     

     

    Appreciate any suggesting improving the DAX function to get the desired output.

    Thanks in advance

    Mohammed

    • Edited by MALI_DBA Thursday, March 8, 2012 1:18 AM
    Thursday, March 8, 2012 1:03 AM

Answers

  • Hi MALI_DBA,

    You could try with,

    =COUNTROWS(FILTER(DATESBETWEEN(Dates[Date],STARTOFMONTH(Dates[Date]),ENDOFMONTH(Dates[Date])),Dates[Date]>=booking[star]&& Dates[date]<= booking[end]))

    Regards.

    César.

    • Marked as answer by MALI_DBA Thursday, March 8, 2012 10:44 PM
    Thursday, March 8, 2012 4:14 PM

All replies

  • Hi MALI_DBA,

    You could try with,

    =COUNTROWS(FILTER(DATESBETWEEN(Dates[Date],STARTOFMONTH(Dates[Date]),ENDOFMONTH(Dates[Date])),Dates[Date]>=booking[star]&& Dates[date]<= booking[end]))

    Regards.

    César.

    • Marked as answer by MALI_DBA Thursday, March 8, 2012 10:44 PM
    Thursday, March 8, 2012 4:14 PM
  • Hi Cesar

    this was helpful.


    Mali

    Thursday, March 8, 2012 10:45 PM