Forum Discussion

SNV's avatar
SNV
Advocate I
8 years ago
Solved

Average by minimum and maximum date by group

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 scor...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    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.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
8 years ago

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's avatar
    SNV
    Advocate I
    8 years ago

    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!

  • SNV's avatar
    SNV
    Advocate I
    8 years ago

    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's avatar
      SNV
      Advocate I
      8 years ago

      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?