DAX Formula for Best Month

[samblackshaw28]

Hi,

I am producing a report for our advisers to show their revenue generated into the business. It would be handy to have a card of some sort to show if an adviser has had their best month in terms of revenue generated since the data started. This is so we can congratulate them. But the card would show as -- if there have been no advisers with their best month. So if we are in august and an adviser has generated revenue which is their maximum, the card would show their name. Is this dax possible and if so could someone give me some pointers on how to form it?

So, in conclusion, DAX expression for maximum revenue when the month is the current month, or something along the lines of that.

Thankyou so much for your help.

[Greg_Deckler]

@samblackshaw28 Sure, sample data would help but it should be along the lines of:

Measure =
  VAR __Today = TODAY()
  VAR __Table = SUMMARIZE( FILTER( 'Table', [Date] <= __Today ), [Advisor], [Year], [Month], "__Value", SUM('Table'[Value]) )
  VAR __MaxValue = MAXX( __Table, [Value] )
  VAR __Year = MAXX( FILTER( __Table, [__Value] = __MaxValue ), [Year] )
  VAR __Month = MAXX( FILTER( __Table, [__Value] = __MaxValue ), [Month] )
  VAR __Result = IF( __Year = YEAR( __Today ) && __Month = MONTH( __Today ), MAXX( FILTER( __Table, [__Value] = __MaxValue ), [Advisor] ), "--" )
RETURN
  __Result

If that doesn't help, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[samblackshaw28]

Hi Greg,

Thankyou for your reply and apologies for the lack of clarity. For compliance reasons I can't sent the data but I can give you the column names which I am using.

So in the dimension table, the table name is '2024Revenue' I have columns for:
a. AdviserName (the advisers name)

b. CashRecieptData (the date column)

c. RevenueDelivered (the revenue generated)

In this table the data isn't grouped by month or anything and is just each transaction line by line, so the summarize function is needed as you have used.

I also have a seperate calender table which has a relationship with the cash receipt date and in this table there is a start of month column.

I also already have a measure for the sum of revenuedelivered, called [totalrevenue], if this helps in the dax.

Sorry if this is even more confusing, but I do appreciate the help. If sampled data is needed I can make some up quickly to help with it.

[Greg_Deckler]

@samblackshaw28 Another clarifying question on this. Is RLS involved where each Advisor sees only their own data? If not, what do want to happen if two or more people hit their best month, concatenate the names together with a comma separator?

[samblackshaw28]

Hi Gregg,

RLS is not involved here as it is only the Finance team who will be viewing this data. Secondly, this is something I was going to query, what happens if 2 advisers hit their best month. If there is DAX that allows them to both be shown then that would be perfect. If not, I'll have to work a way around it.

[Greg_Deckler · Accepted solution]

@samblackshaw28 OK, PBIX is attached below signature. Here is the measure.

WinnerWinnerChickenDinner = 
    VAR __Today = TODAY()
    VAR __Current = YEAR( __Today ) * 100 + MONTH( __Today )
    VAR __Table = 
        ADDCOLUMNS(
            'Table',
            "YearMonth", YEAR( [CaseReceiptData] ) * 100 + MONTH( [CaseReceiptData] )
        )
    VAR __Table1 = SUMMARIZE( __Table, [AdvisorName], [YearMonth], "__Value", SUM( 'Table'[RevenueGenerated] ) )
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table1,
            "__WinnerWinnerChickenDinner",
                VAR __Advisor = [AdvisorName]
                VAR __MaxValue = MAXX( FILTER( __Table1, [AdvisorName] = __Advisor ), [__Value] )
                VAR __YearMonth = MAXX( FILTER( 'Table', [AdvisorName] = __Advisor && [__Value] = __MaxValue ), [YearMonth] )
                VAR __Result = IF( __YearMonth = __Current, 1, 0 )
            RETURN
                __Result
        )
    VAR __Result = CONCATENATEX( FILTER( __Table2, [__WinnerWinnerChickenDinner] = 1 ), [AdvisorName], ", " )
RETURN
    __Result      

[samblackshaw28]

Hi @Greg_Deckler 

Thankyou so much, you're a legend. I really appreciate your help.

Just another question, how do you change the DAX to instead of it being 'today' to pretend we're in July. This is so I can check it works as we only get the august data in september so I won't be able to see if it works until september, does this make sense? 
I've tried changing the __today variable to Date(2024,7,1) for July but it doesn't work. I am very new to DAX so I don't understand how to do it all yet.