Max from related table

[usernj123]

Hello All,

I have 2 tables as shown below.

Months table: (MonthName is text)

TechSpend:

MonthName      Text

Category   Text

Year          Text

Actuals     Currency

TechSpend table has data for all 12 months of 2023 and 1st month of 2024.

Both tables are related using MonthName column. (Months table is on 1 side of Many:1). 

On one of my report, I have a slicer on "Year" and trying to use a Measure to show "Highest MonthName - Total Actuals" in a card.

Total Actuals =

VAR ActualExceptOther = ROUND(CALCULATE(sum(TechSpend[Actuals]), TechSpend[Category] <> "Other")/1000,2)

VAR MaxMonth = CALCULATE(FIRSTNONBLANK(TechSpend[Month],1), FILTER(Months, Months[MonthNumber] = max(Months[MonthNumber])))

RETURN MaxMonth & " - " & ActualExceptOther & "K".

When I run the report with 2024 selected, I get MaxMonth = blank as shown below. Could you please review my code for measure and suggest the changes?

THANKS.

[usernj123]

 Thank you all for quick responses!

[v-tangjie-msft]

Hi @usernj123 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Total Actuals = 
VAR ActualExceptOther = ROUND(CALCULATE(sum(TechSpend[Actuals]), TechSpend[Category] <> "Other")/1000,2)
VAR _table= ADDCOLUMNS(ALL('TechSpend'),"monthnum",MONTH(CONVERT([MonthName]&" "&1,DATETIME)))
var MaxNum=MAXX(FILTER(_table,[Year] =SELECTEDVALUE('TechSpend'[Year])),[monthnum])
var  MaxMonth=CALCULATE(MAX('Months'[MonthName]),FILTER(ALL('Months'),'Months'[MonthNumber]=MaxNum))
RETURN MaxMonth & " - " & ActualExceptOther & "K"

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[nsexton12 · Accepted solution]

I agree with the above comment about creating a proper date table! However, below is a solution anyway. 

----------------------------------------------------------------

VAR FilteredVirtualTable =TOPN(1,FILTER(SUMMARIZE(Months,Months[MonthsName],Months[MonthNum]),Months[MonthsName] IN VALUES(TechSpend[MonthsName])),Months[MonthNum])

VAR MonthNameVirtalTable = SELECTCOLUMNS(FilteredVirtualTable,"Month Name",Months[MonthsName])

RETURN MonthNameVirtalTable 

----------------------------------------------------------------

You needed to add one more step by creating a virtual table that is all matching rows between your months table and tech spend table, and then take the top row of that table after it is sorted by month number. The last step is using the selected columns function to grab the name of the month from that table. 

Please mark as a solution if this worked for you!

[lbendlin]

PLease consider using a proper Dates table instead.  It will make your other calculations so much easier.