Forum Discussion

PBI5851's avatar
PBI5851
Helper V
3 years ago
Solved

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 ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

You may download my PBI file from here.

Hope this helps.