Creating a measure to take MAX based on existing columns

[davinci_cat]

Hi

I have a dashboard with data in the following format:

There is a number of accounts in the same table and for each account there are multiple monthly rows of data. There is a column called PnL. I have created a measure that calculates the percent change between one month and the next for each account, let's call it PnL Monthly Change.  I would like to create a measure that can look at PnL Monthly Change for each account, for the latest month only and give me only the MAX value from all of them. The idea is that if it reaches a certain threshold, an email notification is issued. 

Now, I've struggled with this because I cannot treat the PnL measure as a column. I can't create a calculated column for PnL change because (I think) there are multiple accounts in the same table. Maybe I am thinking about this in a wrong way and there is a simpler solution.

In the end, I would like to create a measure that only looks at the latest month, at all accounts, calculates the Monthly PnL Change for each account and then shows me the MAX value. TIA.

[Anonymous · Accepted solution]

Hi @davinci_cat ,

You can try this DAX:

Max PnL Monthly Change for Latest Month = 
CALCULATE(
    MAXX(
        ALL('Accounts'), // Replace with your actual accounts table name
        [PnL Monthly Change]
    ),
    FILTER(
        ALL('Date'),
        'Date'[Date].[MonthNo] =  = MONTH(TODAY() - DATEDIFF("2023-01-01","2023-02-02",DAY))
    )
)

Best Regards,

Xianda Tang

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

[vanessafvg]

please provide sample data with an example of your expected solution.