DAX Measure - Summing up values between start-end dates in the same table

Question

I have an "Assignments" Table like this:

Date   End Date   Allocation
1111   Alex   AA   11/20/2016   12/30/2016   0.5
2222   Eric   BB   10/20/2016   11/30/2016   0.4
3333   John   CC   10/20/2016   12/30/2016   1
2222   Eric   DD   11/20/2016   1/1/2017        0.5

I also have a simple "Date" Table which DOES NOT have a relationship with the Assignments Table like this:

Date   Month   Month_Text   Year   Month-Year
10/1/2016   10   Oct   2016   Oct-16
10/2/2016   10   Oct   2016   Oct-16
10/3/2016   10   Oct   2016   Oct-16
10/4/2016   10   Oct   2016   Oct-16

Than I have the following DAX measure:

==============================

Sum of Assignments :=
CALCULATE (
    SUMX ( Assignments_Tbl, Assignments_Tbl[Allocation] ),
    FILTER (
        VALUES ( Date_Tbl ),
        Date_Tbl[Date] >= MINX ( Assignments_Tbl, Assignments_Tbl[Start Date] )
            && Date_Tbl[Date] <= MAXX ( Assignments_Tbl, Assignments_Tbl[End Date] )
    )
)
    * CALCULATE (
        DISTINCTCOUNT ( Date_Tbl[Month] ),
        FILTER (
            VALUES ( Date_Tbl ),
            Date_Tbl[Date] >= MINX ( Assignments_Tbl, Assignments_Tbl[Start Date] )
                && Date_Tbl[Date] <= MAXX ( Assignments_Tbl, Assignments_Tbl[End Date] )
        )
    )

==============================

All seems OK when I have the Start Date and End Date as part of my Pivot Table like below:

Sum of Assignments in a pivot table with Start/End Dates

HOWEVER, if I remove the Start Date and End Date from the pivot table, the measure is calculating incorrectly and I can't find why.

For instance, as the pic below, the red circles show 0.9 for a person name "Eric" in in Oct-2016, instead of 0.4.

Sum of Assignments measure calculating incorrectly

I tried many things, but got stuck on this measure. Any idea?

Forgot to mention that I know the issue is at the "MINX/MAXX" because they are returning the entire period when a person has "multiple assignments", but don't know how to solve that. — Scopinho

Scopinho Scopinho · Accepted Answer · 2016-10-25T17:30:38

After some deeper investigation, here is the proper measure:

New Measure =
SUMX (
    Assignments_Tbl,
    CALCULATE (
        SUM ( Assignments_Tbl[Allocation] ) * DISTINCTCOUNT ( Date_Tbl[Month-Year] ),
        FILTER (
            VALUES ( Date_Tbl ),
            Date_Tbl[Date] >= EARLIER ( Assignments_Tbl[Start Date], 1 )
                && Date_Tbl[Date] <= EARLIER ( Assignments_Tbl[End Date], 1 )
        )
    )
)

DAX Measure - Summing up values between start-end dates in the same table

1 Answers