topic Re: Return the max date and associated value in DAX Commands and Tips

Return the max date and associated value

PBIX_COACH — Sun, 23 Apr 2023 04:07:16 GMT

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.

Re: Return the max date and associated value

FreemanZ — Sun, 23 Apr 2023 05:38:38 GMT

hi PBIX_COACH

how does your dataset look like?

Re: Return the max date and associated value

tamerj1 — Sun, 23 Apr 2023 10:10:15 GMT

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

Re: Return the max date and associated value

PBIX_COACH — Sun, 23 Apr 2023 21:24:03 GMT

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.

Re: Return the max date and associated value

tamerj1 — Mon, 24 Apr 2023 13:39:55 GMT

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

Re: Return the max date and associated value

PBIX_COACH — Mon, 24 Apr 2023 13:53:22 GMT

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.

Re: Return the max date and associated value

PBIX_COACH — Mon, 24 Apr 2023 17:06:37 GMT

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 )

Re: Return the max date and associated value

tamerj1 — Mon, 24 Apr 2023 17:18:06 GMT

PBIX_COACH

Can you paste the exact code tgat you have used?

Re: Return the max date and associated value

PBIX_COACH — Mon, 24 Apr 2023 18:24:07 GMT

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

Re: Return the max date and associated value

tamerj1 — Mon, 24 Apr 2023 18:30:22 GMT

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?

Re: Return the max date and associated value

PBIX_COACH — Mon, 24 Apr 2023 19:21:33 GMT

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

Re: Return the max date and associated value

tamerj1 — Mon, 24 Apr 2023 20:06:40 GMT

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?!

Re: Return the max date and associated value

PBIX_COACH — Mon, 24 Apr 2023 20:17:20 GMT

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

Re: Return the max date and associated value

tamerj1 — Tue, 25 Apr 2023 04:22:26 GMT

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.

Re: Return the max date and associated value

PBIX_COACH — Tue, 25 Apr 2023 14:25:12 GMT

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.

Re: Return the max date and associated value

tamerj1 — Tue, 25 Apr 2023 14:46:17 GMT

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

Re: Return the max date and associated value

PBIX_COACH — Tue, 25 Apr 2023 17:25:48 GMT

Bingo! that's it, thanks so much tamerj1