Issue with adding dates / sumifs

[sbenzaquen]

Hi, I need some help to determine the number of days off per person/team on a monthly basis with the input data shown below. The key problem is that some absence requests may cover different months (See example below row 2). 

The way I initially approached this is by splitting the number of days-off impacting every month (columns H and K) in the input data to then use the excel logic of a sumif (in which I add both columns at any given month using columns I and K as criteria ranges). However, this does not work when using a Drax measure (sum(leg1 absence days)+sum(leg2 absence days)) and using either Leg1/leg2 month cut-off in the X-axis. Appreciate if you could please help as I'm relatively new to using PowerBI.

[Anonymous · Accepted solution]

Hi @sbenzaquen ,

1. Add columns to original table:

Hours in 1st Month =
VAR _sym =
    FORMAT ( 'Table'[Start Date], "YYYY-MM" )
VAR _eym =
    FORMAT ( 'Table'[End Date], "YYYY-MM" )
VAR _val =
    IF (
        _sym = _eym,
        [Days/Hours],
        DATEDIFF ( 'Table'[Start Date], ENDOFMONTH ( 'Table'[Start Date].[Date] ), DAY ) - 1
    )
RETURN
    IF ( [Start Date] = [End Date], _val, _val * 24 )

Hours in 2nd Month =
IF (
    [Start Date] = [End Date],
    [Days/Hours] - [Hours in 1st Month],
    [Days/Hours] * 24 - [Hours in 1st Month]
)

2.Create two calculated tables:

Fist table =
DISTINCT (
    SELECTCOLUMNS (
        'Table',
        "YearMonth", FORMAT ( 'Table'[Start Date], "YYYY-MM" ),
        "Member", [Team Member Name]
    )
)

Column =
CALCULATE (
    SUM ( 'Table'[Hours in 1st Month] ),
    FILTER (
        'Table',
        'Table'[Team Member Name] = [Member]
            && FORMAT ( 'Table'[Start Date], "YYYY-MM" ) = [YearMonth]
    )
)

......

3.The final output:

Final =
FILTER ( UNION ( 'Second Table', 'Fist table' ), [Column] <> 0 )

Please take a look at the pbix file here. 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sbenzaquen]

Hi Eyelyn, thank you very much for providing such a great answer. It works perfectly. 

Kind regards,

Salvador