Optimizing performance when using time comparison
Hello,
I have a large dataset where some of the data looks like this:
| Name | Test | Activity | DateStarted | DateCompleted | PercComplete | Key | _TestCompleteDate | _CompletedIndividual | _TestCompleteUpToLastMonth | _TestCompleteUpToLastMonth2 |
| A | x | 1 | 03/02/2022 | 25% | A-x | |||||
| A | x | 2 | 03/02/2022 | 25% | A-x | |||||
| A | x | 3 | 03/03/2022 | 04/04/2022 | 25% | A-x | ||||
| A | x | 4 | 06/03/2022 | 25% | A-x | |||||
| A | y | 1 | 04/02/2022 | 04/02/2022 | 100% | A-y | 04/05/2022 | A | 1 | 1 |
| A | y | 2 | 04/02/2022 | 04/02/2022 | 100% | A-y | 04/05/2022 | A | 1 | 1 |
| A | y | 3 | 04/05/2022 | 04/05/2022 | 100% | A-y | 04/05/2022 | A | 1 | 1 |
| B | z | 1 | 10/02/2022 | 60% | B-z | |||||
| B | z | 2 | 10/02/2022 | 10/02/2022 | 60% | B-z | ||||
| B | z | 3 | 10/02/2022 | 10/02/2022 | 60% | B-z | ||||
| B | z | 4 | 10/03/2022 | 10/13/2022 | 60% | B-z | ||||
| B | z | 5 | 10/05/2022 | 60% | B-z | |||||
| C | x | 1 | 10/10/2022 | 10/11/2022 | 100% | C-x | 10/13/2022 | C | 1 | 1 |
| C | x | 2 | 10/12/2022 | 10/12/2022 | 100% | C-x | 10/13/2022 | C | 1 | 1 |
| C | x | 3 | 10/12/2022 | 10/12/2022 | 100% | C-x | 10/13/2022 | C | 1 | 1 |
| C | x | 4 | 10/12/2022 | 10/13/2022 | 100% | C-x | 10/13/2022 | C | 1 | 1 |
| C | y | 1 | 10/13/2022 | 10/13/2022 | 100% | C-y | 03/29/2023 | C | ||
| C | y | 2 | 10/13/2022 | 10/13/2022 | 100% | C-y | 03/29/2023 | C | ||
| C | y | 3 | 10/29/2022 | 03/29/2023 | 100% | C-y | 03/29/2023 | C | ||
| D | z | 1 | 01/12/2023 | 01/12/2023 | 40% | D-z | ||||
| D | z | 2 | 01/12/2023 | 01/12/2023 | 40% | D-z | ||||
| D | z | 3 | 02/01/2023 | 40% | D-z | |||||
| D | z | 4 | 02/01/2023 | 40% | D-z | |||||
| D | z | 5 | 02/01/2023 | 40% | D-z |
Where
Columns:
PercComplete = DIVIDE(CALCULATE(COUNTA('Table'[DateCompleted]), ALLEXCEPT('Table','Table'[Key])), CALCULATE(COUNTA('Table'[Activity]), ALLEXCEPT('Table','Table'[Key])), 0)
_CompletedIndividual = IF([PercComplete]=1, [Name], BLANK())
_TestCompleteDate = IF([PercComplete]=1, CALCULATE(MAX([DateCompleted]), ALLEXCEPT('Table','Table'[Key])))
_TestCompleteUpToLastMonth = IF([_TestCompleteDate]<=TODAY()-30 && [_CompletedIndividual]<>BLANK(),1)
TestCompleteIndividuals = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[_TestCompleteUpToLastMonth]=1))
or column:
_TestCompleteUpToLastMonth2 =
var maxcompldate =CALCULATE(MAX([_TestCompleteDate]), ALLEXCEPT('Table','Table'[Key]))
return IF(maxcompldate<=TODAY()-30 && [_CompletedIndividual]<>BLANK(),1)
TestCompleteIndividuals2 = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[_TestCompleteUpToLastMonth2]=1))
What I am trying to achieve is calculate how many "Name"-s have completed all the "Activities" of a "Test" up until 30 days ago (measures TestCompleteIndividuals and TestCompleteIndividuals2).
A basic calculation of who completed all the activities is working just fine with the report visuals, but when I add the TestCompleteIndividuals and TestCompleteIndividuals2 measures working with [_TestCompleteDate]<=TODAY()-30 or maxcompldate<=TODAY()-30, the visuals that contain any of these measures take so long to load, they sometimes crash.
Is there a way to calculate how many "Name"-s have passed all the activities in a test up until a month ago that will not burden the report performance?
Many thanks for any input on the matter.
