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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

Is there a reason to redefine the measure as a var in step 2?

 

Just so I can confirm, the technique here is get the max observation time from the observed values table - this will always be the latest date in the table - scalar value.

 

Next, get the value for that date, and again this is going to be measured for the entire table - so it returns a scalar value.

 

Last, grab the latest calendar dim date - again, for context of filters and/or rows in a visual this is a scalar along an axis or in table/matrix. build a summary table of each person, their latest reading date and reading of that date, and then average the value of the rows in that table where the calendar dimension (which is related to the observation date) is less that the max date.

 

I think that will work - I am going to test it in the sample pbi and see how it works when new values are brought into the observations fact table.