Forum Discussion
Capture average based on slicer
Hello folks,
please can you help me figure out on how to capture the average days a member has acted based on the ActDate slicer. So when the slicer has the dates, i need to capture the latest row for the PerID within that range and capture the Actdays( no need to sum the previous rows)
ex:
| ID | PerID | ActDate | Actdays |
| 1 | A123 | 1/1/2023 | 1 |
| 2 | A123 | 1/2/2023 | 2 |
| 3 | A123 | 1/3/2023 | 3 |
| 4 | A123 | 1/4/2023 | 4 |
| 5 | A123 | 1/5/2023 | 5 |
| 6 | A123 | 1/6/2023 | 6 |
| 7 | A123 | 1/7/2023 | 7 |
| 8 | A123 | 1/8/2023 | 8 |
| 9 | B123 | 1/6/2023 | 1 |
| 10 | B123 | 1/7/2023 | 2 |
| 11 | B123 | 1/8/2023 | 3 |
| 12 | B123 | 1/9/2023 | 4 |
| 13 | C123 | 1/5/2023 | 1 |
| 14 | C123 | 1/6/2023 | 2 |
| 15 | C123 | 1/7/2023 | 3 |
| 16 | C123 | 1/8/2023 | 4 |
| 17 | C123 | 1/9/2023 | 5 |
| 18 | C123 | 1/10/2023 | 6 |
| 19 | C123 | 1/11/2023 | 7 |
| 20 | C123 | 1/12/2023 | 8 |
So when the slicer is from 1/1/23 to 1/10/23,
the total members are 3
and total actdays are 18
- A123 @ 8, B123 @ 4 and C123 @6
This results in an average of 6
So when the slicer is from 1/4/23 to 1/7/23,
the total members are 3
and total actdays are 12
- A123 @ 7, B123 @ 2 and C123 @ 3
This results in an average of 3
So when the slicer is from 1/10/23 to 1/12/23,
the total members are 1
and total actdays are 8
- A123 - 0, B123 -0 and C123 - 8
This results in an average of 8
Hi,
Please check the below picture and the attached pbix file.
Count members: = COUNTROWS( SUMMARIZE( Data, PerID[PerID]))Actdays: = VAR _lastdatecalendar = MAX ( 'Calendar'[Date] ) VAR _lastdateactdate = MAXX ( FILTER ( ALLSELECTED ( Data ), Data[PerID] = MAX ( PerID[PerID] ) && Data[ActDate] <= _lastdatecalendar ), Data[ActDate] ) RETURN IF ( HASONEVALUE ( PerID[PerID] ), CALCULATE ( SUM ( Data[Actdays] ), 'Calendar'[Date] = _lastdateactdate ) )Total actdays: = SUMX( VALUES( PerID[PerID]), [Actdays:] )Average actdays: = AVERAGEX( VALUES( PerID[PerID]), [Actdays:] )
2 Replies
Hi,
Please check the below picture and the attached pbix file.
Count members: = COUNTROWS( SUMMARIZE( Data, PerID[PerID]))Actdays: = VAR _lastdatecalendar = MAX ( 'Calendar'[Date] ) VAR _lastdateactdate = MAXX ( FILTER ( ALLSELECTED ( Data ), Data[PerID] = MAX ( PerID[PerID] ) && Data[ActDate] <= _lastdatecalendar ), Data[ActDate] ) RETURN IF ( HASONEVALUE ( PerID[PerID] ), CALCULATE ( SUM ( Data[Actdays] ), 'Calendar'[Date] = _lastdateactdate ) )Total actdays: = SUMX( VALUES( PerID[PerID]), [Actdays:] )Average actdays: = AVERAGEX( VALUES( PerID[PerID]), [Actdays:] )