cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

Finding out the yyyy-mmm when maximum collection was recorded

Hello Power BI Community,

I am currently trying to assess the appropriateness of the level of credit limits given to customers.  In order to do so, I have assessed the maximum collections in a month by writing the following formula.

I'd like to find out which were the yyyy-mm which recorded highest collections for each customer.  Please could you let me know the formula to get this information?

Thank you.

1 ACCEPTED SOLUTION
Super User

Hi @DataNinja777
If I understood correctly.
You need 2 measures.
1. For max collections per month :

Highest Collections=

Maxx(

SUMMARIZE (

'yourtable',

'calendar'[yyyy-mm],

"Total collections", SUM('yourtable'[collections])    ),

[collections])
2 For the month with maximum collections

Max Month =

VAR vTable = VALUES('calendar'[yyyy-mm] )

VAR MaxCollections = [Highest Collections]

VAR Keepmin = FILTER( vTable, [collections] =MaxCollections )

VAR min_month = IF( MaxCollections >0, CONCATENATEX( Keepmin, [yyyy-mm], ", "))

RETURN

max_month
I prepared PBIX with the same logic for a month with the lowest sales scenario for one of my lections :

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

Regards,
Rita Fainshtein | Microsoft MVP
5 REPLIES 5
Super User

Happy to help 🙂

Regards,
Rita Fainshtein | Microsoft MVP
Super User

Hi,

Does this measure work (drag it to a card visual)

Measure = calculate(max('calendar'[yyyy-mm]),filter(Data[Customer],[Collections]=[Max monthly collections]))

If it does not work, then share the download link of the PBI file and show the expected result there.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

The formula didn't produce my required output and just produced the max date of November 2023.  Thank you for your time anyway.

Super User

Hi @DataNinja777
If I understood correctly.
You need 2 measures.
1. For max collections per month :

Highest Collections=

Maxx(

SUMMARIZE (

'yourtable',

'calendar'[yyyy-mm],

"Total collections", SUM('yourtable'[collections])    ),

[collections])
2 For the month with maximum collections

Max Month =

VAR vTable = VALUES('calendar'[yyyy-mm] )

VAR MaxCollections = [Highest Collections]

VAR Keepmin = FILTER( vTable, [collections] =MaxCollections )

VAR min_month = IF( MaxCollections >0, CONCATENATEX( Keepmin, [yyyy-mm], ", "))

RETURN

max_month
I prepared PBIX with the same logic for a month with the lowest sales scenario for one of my lections :

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

Regards,
Rita Fainshtein | Microsoft MVP
Super User

Thank you @Ritaf1983 !

I copied and pasted your formula, and switched min and max and it worked perfectly.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.