Return the max date and associated value

[PBIX_COACH]

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.

[PBIX_COACH]

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.

[tamerj1]

@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] ) ) )

[PBIX_COACH]

@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.

[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]

@PBIX_COACH 

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] )))

[tamerj1]

@PBIX_COACH 

It should be year quarter number (integer data type). But even though it should work at least if you filter one year. Would you plese place it in the table visual and provide a screenshot of the results?

[PBIX_COACH]

@tamerj1  please see below the Measure max is showing the correct value LastDate2 is using the formula you recommended.

[tamerj1]

@PBIX_COACH 

I just noticed that somehow I changed the MAXX as per my first reply to SUMX by mistake. It should be 

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
MAXX ( T3, CALCULATE ( SUM ( 'Maintbl'[Value] ) ) )

however, I still have some concerns; why the value shows at Q1 not Q4 and why it shows 0 for other quarters and whay the total is 0?!

[PBIX_COACH]

@tamerj1 

The 0 might be due to no data in Q2-Q4 for 2023. the formula output is still giving me the incorrect result.

This is what I see when I change the filter to 2022

[tamerj1]

@PBIX_COACH 
I created a sample file and it seems to be working with no issues. Please modify the attached sample file to reflect the actual case and clarify where exactly the problem is and share with me in order to amend if needed.

[PBIX_COACH]

thanks 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 · Accepted solution]

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_COACH]

Bingo! that's it, thanks so much @tamerj1 

[tamerj1]

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] )

[FreemanZ]

hi @PBIX_COACH 

how does your dataset look like?