Forum Discussion
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 Qtr header) of the three categories lines and sum them. I have a Dim table for the date that I am using to get the YYYY QQ.
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] )
16 Replies
hi PBIX_COACH
how does your dataset look like?
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] )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.
LastDate :=CALCULATE (SUM ( mainTbl[value] ),LASTDATE ( mainTbl[date] ))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.
