Capture average based on slicer

[PBI5851]

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

[Jihwan_Kim · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Jihwan_Kim · Accepted solution]

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