Forum Discussion

usernj123's avatar
usernj123
New Member
2 years ago
Solved

Max from related table

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.

 

 
Need Help
  • nsexton12's avatar
    nsexton12
    2 years ago

    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!
     

4 Replies

  • nsexton12's avatar
    nsexton12
    Icon for Resolver II rankResolver II
    2 years ago

    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!
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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