cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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 in these 2 tables:

 TestId ReleaseDate Department Test1 11/1/2018 A Date StartDate EndDate Test2 11/2/2018 A 11/1/2018 11/1/2018 11/7/2018 Test3 11/3/2018 A 11/2/2018 11/2/2018 11/8/2018 Test4 11/1/2018 B 11/3/2018 11/3/2018 11/9/2018 Test5 11/2/2018 A 11/4/2018 11/4/2018 11/10/2018 Test6 11/3/2018 B 11/5/2018 11/5/2018 11/11/2018 Test7 11/4/2018 C 11/6/2018 11/6/2018 11/12/2018 Test8 11/5/2018 C 11/7/2018 11/7/2018 11/13/2018 Test9 11/6/2018 D 11/8/2018 11/8/2018 11/14/2018 Test10 11/7/2018 A 11/9/2018 11/9/2018 11/15/2018 Test11 11/8/2018 A 11/10/2018 11/10/2018 11/16/2018 Test12 11/9/2018 A 11/11/2018 11/11/2018 11/17/2018 Test13 11/1/2018 B 11/12/2018 11/12/2018 11/18/2018 Test14 11/2/2018 B 11/13/2018 11/13/2018 11/19/2018 Test15 11/3/2018 C 11/14/2018 11/14/2018 11/20/2018 Test16 11/4/2018 A 11/15/2018 11/15/2018 11/21/2018 Test17 11/5/2018 A 11/16/2018 11/16/2018 11/22/2018 Test18 11/6/2018 A 11/17/2018 11/17/2018 11/23/2018 Test19 11/7/2018 A 11/18/2018 11/18/2018 11/24/2018 Test20 11/8/2018 A 11/19/2018 11/19/2018 11/25/2018 Test21 11/1/2018 B 11/20/2018 11/20/2018 11/26/2018 Test22 11/20/2018 C Test23 11/15/2018 B Test24 11/2/2018 A

I am trying to calculate no of test cases based on ReleaseDate between StartDate and EndDate. User can select date any date range using slicer on PowerBI dashboard. If user selects Department also, then no of test cases should be calculated based on department selected and between StartDate and EndDate.

The sample result I am expecting is as below:

 Case 1 Selected date from date slicer is 11/1/2018 and all department Date TestReleaseCount 11/1/2018 19 Average 19 Case 2 Selected date from date slicer is 11/1/2018 and "A" department from Department Slicer Date TestReleaseCount 11/1/2018 10 Average 10 Case 3 Selected date range from date slicer is 11/1/2018 to 11/4/2018 and all department Date TestReleaseCount 11/1/2018 19 11/2/2018 17 11/3/2018 14 11/4/2018 11 Average 15.25 case 4 Selected date range from date slicer is 11/1/2018 to 11/4/2018 and "A" department from date slicer Date TestReleaseCount 11/1/2018 10 11/2/2018 11 11/3/2018 9 11/4/2018 8 Average 9.5

I came up with below formula, but it is not giving me desired result:

VAR abc = SELECTEDVALUE(TestDetails[Department], "ALL")

CALCULATE(COUNT(TestDetails[TESTID]),

FILTER(ALL(TestDetails), (TestDetails[Department] = abc)

&& (TestDetails[ReleaseDate]>=Min(Date[StartDate]))

&& (TestDetails[ReleaseDate]<=Max(Date[EndDate]))

)

)

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Supplier

## Re: Cumulative sum based on date range from slicer

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])`
3 REPLIES 3
Community Support

## Re: Cumulative sum based on date range from slicer

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

Frequent Visitor

## Re: Cumulative sum based on date range from slicer

@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.

Solution Supplier

## Re: Cumulative sum based on date range from slicer

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])`

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors