Forum Discussion
PBIX_COACH
Helper II
Helper IIReturn 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...
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] )
PBIX_COACHHelper II
Helper IIthanks for this tamerj1 But to clarify the cardshould be the sum of line 1,4 and 8 at the max date which is total of 83,399 and so if I also had a slicer and selected 2023 for example It will give the max date and return the sum again for the same lines. I will replicate my scenario using this file and repost it but this helps so that you can understand my context.
tamerj1
Community Champion
Community ChampionThank 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] )
- PBIX_COACH
Bingo! that's it, thanks so much tamerj1