Forum Discussion

PBIX_COACH's avatar
PBIX_COACH
Icon for Helper II rankHelper II
3 years ago
Solved

Return the max date and associated value

Hi Team I need some help to sum line 1, 4 and 8 respectively however I'm having a hard time doing so without adding the entire line. Essentially I need to grab just the values at the max date (year Q...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Thank you PBIX_COACH 

    That is not a big issue. Please try

    LastDate2 = 
VAR T1 =
    SUMMARIZE ( 'Maintbl', Sort_tbl[Line_Items], CalendarDim[Year-Q], "@Value", SUM ( 'Maintbl'[Value] ) )
VAR T2 =
    FILTER ( T1, [@Value] > 0 && Sort_tbl[Line_Items] IN { "Line 1","Line 4","Line 8" } )
VAR T3 =
    TOPN ( 1, T2, CalendarDim[Year-Q] )
RETURN
    SUMX ( T3, [@Value] )

     

     

     

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi PBIX_COACH 

please try something like

=
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[Category], 'Date'[YearQuarterNumber] )
VAR T2 =
TOPN ( 1, T1, 'Date'[YearQuarterNumber] )
VAR T3 =
FILTER ( T2, 'Table'[Category] IN { "Line1", "Line4", "Line8" } )
RETURN
MAXX ( T3, [Measure] )