Forum Discussion
Return 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] )
No dice on this one tamerj1 FreemanZ I think I'm looking for a semi-additive calculation that will end up in a card visual. to add more context, the model is set up as followed.
Here is my model set up
And here is what I'm trying to achieve. I am using a switch formula to get the respective line item value which is working great. the trouble is I need the create a formula that gives the values at the max date (year Qtr header) based on whatever year I have in my slicer and then have the label line items highlighted summed to compute the valuelisted in the card.
I am using this formula which works, however, I only get values for year 2022 that have all the values complete however when I change my slicer to 2023 I get a blank result.
So sorry I cant post the PBIX file but I could really use the help on this on.
PBIX_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] ) ) )tamerj1 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.
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 )