Average by minimum and maximum date by group

[SNV]

Hello community,

I am trying to get an average per unique ID on the minimum and maximum date in a specified period by group.

The table has columns which contains the groupname, ID, date and score

I want to get the average by group, by selecting the row with the minimum date per ID and maximum date per ID, and averaging all the minimums and averaging all the maximums.

A user can use a slicer to set a period, then within that period the minimum date and maximum date per ID will need to be selected, this happens for every ID and from all the minimums and maximums the average is calculated per group.

For added difficulty, ID's and date's can occur multiple times, so when Group 1, ID 12, 01-01-2017, 5 and Group 1, ID 12, 01-01-2017, 3 both appear in the table, the average for that ID should be 4 and 4 should be used for the average in the group.

Any help will be much appreciated

[Eric_Zhang · Accepted solution]

@SNV

Based on my understanding, it seems that you're looking for an output as below.

If this is your case, you can reference below measure. See more details in the attached pbix file.

Measure 2 =
VAR Tbl1 =
    SUMMARIZE (
        FILTER (
            yourTable,
            yourTable[date] = yourTable[max date]
                || yourTable[date] = yourTable[min date]
        ),
        yourTable[groupname],
        yourTable[id],
        "AVG_PER_ID_ON_MAX&&MIN_DATE", AVERAGE ( yourTable[score] )
    )
RETURN
    AVERAGEX ( Tbl1, [AVG_PER_ID_ON_MAX&&MIN_DATE] )

For more specific suggestion, please post some sample data and expected output. Even better you can upload the pbix file to OneDrive or any web drive and share the download link. Do note to mask sensitive data before uploading.

[SNV]

Thank you for your solution. It worked like a charm.

I have an additional question. Will it be possible to get the Average per Group for all the minimum dates and maximum dates?

Going by your example,

If a user selects a date range via slicer from 01-01-2017 through 08-01-2017,

would it be possible to get the average of all the minimums (closest to the first date of the slicer) and the maximums (closest to the end date of the slicer) like in the attached screenshot?

Thanks in advance for your answer.

Kind regards.

Add.

What I'm looking for is getting the average of all the ID's on the date closest to the lower limit of the date range (selected via the slicer) and the average of all the ID's on the date closest to the upper limit of the date range (selected via the slicer)

[SNV]

I think I've got the solution I'm looking for.

By using the accepted solution and reworking it a bit I came to the following solution:

AVG MIN DATE = 
      VAR Tbl2 = 
       SUMMARIZE(
         FILTER('myTable';
                'myTable'[Date]='myTable'[Minimum Date]);
'myTable'[groupName];
'myTable'[ID];
"AVG_PER_ID_ON_MIN_DATE";AVERAGE('myTable'[score]))
RETURN 
  AVERAGEX(Tbl2;[AVG_PER_ID_ON_MIN_DATE])

 

The problem I'm now faced with is this. It takes the first occuring and last known date to calculate with. A slicer doesn't influence this behaviour. So if i have a slicer for date range 1-1-2017 through 31-1-2017, it will use the first date it sees in the data source, ex. 16-11-2015 instead of looking in the selected range.

Anybody can help me with this?

[SNV]

That is the answer I am looking for. I will try this with my own file, will reply in a couple of days.

Thanks in advance for your solution!