cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prl Frequent Visitor
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]))

 )

)

 

Please help.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
AlexisOlson Member
Member

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

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

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

3.png

 

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 Frequent Visitor
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.

 

Highlighted
AlexisOlson Member
Member

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 131 members 1,684 guests
Please welcome our newest community members: