Forum Discussion
Helper IIReturn the max date and associated value
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] )
Community ChampionPBIX_COACH
The only thing that is not clear is the only thing that you did not clarify at all. Why Lines 1, 4 and 8?
Again I propose the same solution with minor modification
LastDate :=
VAR T1 =
SUMMARIZE ( 'mainTbl', 'sort_table'[Line Item], 'cal_tbl'[YearQuarterNumber] )
VAR T2 =
TOPN ( 1, T1, 'cal_tbl'[YearQuarterNumber] )
VAR T3 =
FILTER ( T2, 'sort_table'[Line Item] IN { "Line1", "Line4", "Line8" } )
RETURN
SUMX( T3, CALCULATE ( SUM ( 'sort_table'[Value] ) ) )
Helper IItamerj1 The reason for line 1,4,and 8 is that these values need to be added together and displayed in a card visual. let me give it a shot and get back to you if it dose not work.
Thanks for the assist.
- PBIX_COACH
tamerj1 result not working and I am getting an incorrect value...I feel like I'm close but I can't seem to get the measure to work so that I can add it to a card visual. The below formula is the closest I have gotten but it only works when I use a table visual with the YearQtr value-added and if I remove it shows blank.
Measure Max = VAR LastDT = MAX('Maintbl'[Date]) Return CALCULATE(SUM('Maintbl'[Value]),'Maintbl'[Line_Items] in {"Line1","Line2","Line3,"Line4"}, 'Maintbl'[Date]= LastDT )- tamerj1
Can you paste the exact code tgat you have used?
- PBIX_COACH
tamerj1 See below
LastDate2 = VAR T1 = SUMMARIZE ( 'Maintbl', Sort_tbl[Line_Items], CalendarDim[Year-Q] ) VAR T2 = TOPN ( 1, T1, CalendarDim[Year-Q] ) VAR T3 = FILTER ( T2, Sort_tbl[Line_Items] IN { "Line1","Line4","Line8" } ) RETURN SUMX( T3, CALCULATE ( SUM ( 'Maintbl'[Value] )))