Forum Discussion

scabral's avatar
scabral
Icon for Helper IV rankHelper IV
6 years ago
Solved

retrieve rows with max value within a group based on date filter

Hi,   i have the following table with some sample data similar to what we have in our model:   Claim Id Damage Id Location Id Seq # Reserve Date Reserve Value Reserve Status 1 99 33...
  • Anonymous's avatar
    Anonymous
    6 years ago

     

     

    // Assumptions:
// Apart from [Reserve Status] all other columns
// in the fact table T should be hidden. Let's 
// assume that the AsOfDateSlicer table that stores
// dates is DISCONNECTED from T.

[Total Reserve] =
var __asOfDate = SELECTEDVALUE( AsOfDateSlicer[Date] )
var __filter =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(            
                T,
                T[ClaimId],
                T[DamageID],
                T[LocationID]
            ),
            "@MaxSeq",
                CALCULATE( MAX( T[Seq #] ) )
        ),
        KEEPFILTERS( T[Reserve Status] = "approved" ),
        T[Reserve Date] <= __asOfDate
    )
var __result =
    CALCULATE(
        SUM( T[Reserve Value] ),
        treatas(
            __filter,
            T[ClaimId],
            T[DamageID],
            T[LocationID],
            T[Seq #]
        ),
        ALL( T )
    )
RETURN
    __result

     

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

scabral 

Hope this solution fits your expected results?
I created a calendar table and linked it to adjust the as of date to the end date in the slicer.

 

Measure = 
VAR T = 
	CALCULATETABLE(
		GROUPBY(
			'Table',
	     	'Table'[Claim Id],'Table'[Damage Id],'Table'[Location Id],         
	        "M", MAXX(CURRENTGROUP(),'Table'[Seq #])
	    ) ,	
	'Table'[Reserve Status] = "approved"
    )
    
VAR T2 = 

FILTER(
	GENERATE(	
		SELECTCOLUMNS( T,
			"CID", 'Table'[Claim Id],"DID", 'Table'[Damage Id],"LID", 'Table'[Location Id],
			"SEQ", [M]
		),		
		'Table'
	),
	'Table'[Claim Id] = [CID] && 'Table'[Seq #] = [SEQ]
)
	
RETURN

SUMX(T2, 'Table'[Reserve Value])

 

 

 

________________________

If my answer was helpful, please mark it as a solution

Click on the Thumbs-Up icon if you like this reply šŸ™‚

YouTube
LinkedIn