Forum Discussion

prl's avatar
prl
Microsoft 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])
prl's avatar
prl
Microsoft 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
Super 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])