Forum Discussion

prl's avatar
prl
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

Cumulative sum based on date range from slicer

I am new to DAX formulas. I have spent ample amount of time to achieve my desired result, but I am not able to find solution. I have TestDetails table and Date table. Below are details of contents i...
Need Help
Show and Tell
  • AlexisOlson's avatar
    AlexisOlson
    7 years ago

    Give this a shot for the count:

     

    TestReleaseCount = 
VAR MinDate = MIN(DateTable[Date])
VAR MaxDate = MAX(DateTable[Date])
VAR DateList = 1
RETURN
CALCULATE(
    COUNT(TestDetails[TestId]),
    FILTER(
        ALL(DateTable[Date]),
        DateTable[Date] <= LOOKUPVALUE(DateTable[EndDate], DateTable[Date], MaxDate) &&
        DateTable[Date] >= LOOKUPVALUE(DateTable[StartDate], DateTable[Date], MinDate)
    )
)

    If you want the average, then you can do that with another measure that references the above:

     

    Avg = AVERAGEX(VALUES(DateTable[Date]), [TestReleaseCount])
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi prl

For Case1 and Case2, you could create measures as below

min_selected = MIN('Date table'[StartDate])

max_selected = MAX('Date table'[EndDate])

Measure =
CALCULATE (
    COUNT ( TestDetails[TestId] ),
    FILTER (
        ALLSELECTED ( TestDetails ),
        [ReleaseDate] >= [min_selected]
            && [ReleaseDate] <= [max_selected]
    )
)

 

For Case3 and Case4, it is a bit complex, i'm working on this and will update as soon as possible.

 

Best Regards

Maggie

prl's avatar
prl
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

v-juanli-msft

For case 1 and 2 the provided solution works fine. Thank you! 

Looking forward to solution for case 3 and case 4.

 

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    7 years ago

    Give this a shot for the count:

     

    TestReleaseCount = 
VAR MinDate = MIN(DateTable[Date])
VAR MaxDate = MAX(DateTable[Date])
VAR DateList = 1
RETURN
CALCULATE(
    COUNT(TestDetails[TestId]),
    FILTER(
        ALL(DateTable[Date]),
        DateTable[Date] <= LOOKUPVALUE(DateTable[EndDate], DateTable[Date], MaxDate) &&
        DateTable[Date] >= LOOKUPVALUE(DateTable[StartDate], DateTable[Date], MinDate)
    )
)

    If you want the average, then you can do that with another measure that references the above:

     

    Avg = AVERAGEX(VALUES(DateTable[Date]), [TestReleaseCount])