Forum Discussion

jagorny's avatar
jagorny
Regular Visitor
9 years ago
Solved

Iterating calculations over a table with a max value per group prior to a given date

I've been trying to combine multiple patterns to make this work but I can't get the nesting order right for the tables/results that I want.   I have a table with a1c values that are collected on a ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi jagorny,

     

    1. We need two measures to get the latest "value" and "obs_date".

     

    LatestDate =
MAX ( 'Table1'[obs_date] )
    RespectiveValue =
VAR latestdate = MAX ( 'table1'[obs_date] )
RETURN
    CALCULATE ( MAX ( 'Table1'[value] ), 'Table1'[obs_date] = latestdate )

     

     

     

     

     

     

     

     

     

     

    2. Maybe you could try this:

     

    avg =
VAR maxdate =
    MAX ( 'DimCalendar'[Date] )
RETURN
    CALCULATE (
        AVERAGEX (
            SUMMARIZE (
                Table1,
                'Table1'[person_nbr],
                "LDate", MAX ( 'Table1'[obs_date] ),
                "RValue", [RespectiveValue]
            ),
            [RValue]
        ),
        FILTER ( ALL ( DimCalendar ), DimCalendar[Date] <= maxdate )
    )

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

jagorny's avatar
jagorny
Regular Visitor
9 years ago

Say you have a table structure:

person_nbr,value,obs_date
123,6.2,12/1/2014
123,8.3,1/18/2015
123,9.0,3/12/2016
123,7.2,6/8/2017
143,10.4,4/15/2012
143,8.9,7/4/2013
143,7.0,11/15/2015
263,9.8,5/15/2012
263,7.2,9/30/2015
263,4.5,11/20/2016
263,6.2,12/01/2014

First I am trying to get the latest reading for each person, e.g. unique person_nbr

person_nbr,value,obs_date

123,7.2,6/8/2017
143,7.0,11/15/2015
263,4.5,11/20/2016

Here's the challenge I guess, that list needs to be dynamic depending on a date context. So while the results for today would be the table above, the results for 2/16/2015 would be:

person_nbr,value,obs_date
123,8.3,1/18/2015
143,8.9,7/4/2013
263,6.2,12/01/2014

I'm trying to get the average of those max scores limited for a given date context. The endgame I am aiming at is a plottable running average of the latest scores recorded for each person.

date,avg_value
11/1/2015,7.43
11/2/2015,7.43
...
11/14/2015,7.43
11/15/2015,7.0
11/16/2015,7.0
...
11/19/2016,7.0
11/20/2016,5.75
11/21/2016,5.75
...
6/7/2017,5.75
6/8/2017,6.23
6/9/2017,6.23

I'd like to be able to plot that running average on a DimCalendar[Date] axis.