topic Re: How to get Max and Min Amount by Month in DAX Commands and Tips

How to get Max and Min Amount by Month

danyeungw — Wed, 12 Oct 2022 22:23:42 GMT

I need to find the max and the min amount in the month for the whole year. I created some data as examples. I need to get the amounts in column 4. Can you please help? Thanks.

Year Month	Date	Amount
2022-08	8/21/2022	$393.91	$703.76	Max in August
2022-08	8/23/2022	$18.32	$18.32	Min in August
2022-08	8/25/2022	$468.14
2022-08	8/27/2022	$128.13
2022-08	8/29/2022	$703.76
2022-08	8/31/2022	$653.54
2022-09	9/1/2022	$836.01	$836.01	Max in September
2022-09	9/2/2022	$400.74	$73.97	Min in September
2022-09	9/6/2022	$117.92
2022-09	9/7/2022	$110.67
2022-09	9/8/2022	$772.33
2022-09	9/9/2022	$457.63
2022-09	9/12/2022	$429.05
2022-09	9/13/2022	$334.74
2022-09	9/14/2022	$607.19
2022-09	9/15/2022	$73.97

Re: How to get Max and Min Amount by Month

Shaurya — Wed, 12 Oct 2022 22:47:49 GMT

Hi danyeungw,

You can use the following formula to create a table with 3 columns: Month, Max Value in that month and Min Value

Max and Min = SUMMARIZE('Table','Table'[Month],"Max",MAX('Table'[Amount]),"Min",MIN('Table'[Amount]))

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog:

Re: How to get Max and Min Amount by Month

Anonymous — Thu, 13 Oct 2022 07:44:09 GMT

Hi danyeungw ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two measures as below to get it:

Max = CALCULATE ( MAX ( 'Table'[Amount] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year Month] = SELECTEDVALUE ( 'Table'[Year Month] ) ) )

Min = CALCULATE ( MIN ( 'Table'[Amount] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year Month] = SELECTEDVALUE ( 'Table'[Year Month] ) ) )

Best Regards

Re: How to get Max and Min Amount by Month

danyeungw — Thu, 13 Oct 2022 14:57:57 GMT

Thanks Anonymous and Shaurya. Acutally the date columns are from the date hierarchy. Sorry I example was misleading. How do I concatinate the Year and Month? I tried to use concatinate, but it seems it can only concatinate the Measure. Here is the table.

Year	Month	Day	Amount
2022	August	21	393.91
2022	August	23	18.32
2022	August	25	468.14
2022	August	27	128.13
2022	August	29	703.76
2022	August	31	653.54
2022	September	1	836.01
2022	September	2	400.74
2022	September	6	117.92
2022	September	7	110.67
2022	September	8	772.33
2022	September	9	457.63
2022	September	12	429.05
2022	September	13	334.74
2022	September	14	607.19
2022	September	15	73.97

Re: How to get Max and Min Amount by Month

Shaurya — Thu, 13 Oct 2022 18:21:33 GMT

Hi danyeungw,

You can merge the year and date columns in Power Query, change the data type to Date and then use the formula in my original reply in DAX.

M Code after merging and changing the data type:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZG9DsIwDITfJXNlxXYSNyMSKxMTqjoUqYKlBUE68PbY3ZASlsvPfbFyumFwl3l6uc6dHmu563qcPqqH5bGtxY3d4MgT2c12295FN4QqnBky1n1WwR6Y6nZUCakHDHVf7Dmpz3U/q4hnkFT12b6XIkP8mX+en2VerrNFNaLnBB5bhJ2D9yDNGcnmoECmFrHnQA9JWkRvlBAwtwjLGqJAahK4f5Uy+NhErBDm8CeNdqGRvADmJmK9ibauccYv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", Int64.Type}, {"Month", type text}, {"Day", Int64.Type}, {"Amount", type number}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Year", type text}}, "en-US"),{"Month", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Month Year"}}), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Month Year", type date}}) in #"Changed Type2"

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog:

Re: How to get Max and Min Amount by Month

danyeungw — Thu, 13 Oct 2022 22:55:59 GMT

Hi Anonymous ,

Your code work perfect for one transaction per day. How can I get the min and max per day with multiple transaction for each day? Thanks.

Re: How to get Max and Min Amount by Month

Anonymous — Fri, 14 Oct 2022 02:54:32 GMT

Hi danyeungw ,

You can create two measures as below to get it, please find the details in the attachment.

Min = CALCULATE ( MIN ( 'Table'[Amount] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] ) && 'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] ) && 'Table'[Day] = SELECTEDVALUE ( 'Table'[Day] ) ) )

Max = CALCULATE ( MAX ( 'Table'[Amount] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] ) && 'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] ) && 'Table'[Day] = SELECTEDVALUE ( 'Table'[Day] ) ) )

Best Regards

Re: How to get Max and Min Amount by Month

danyeungw — Fri, 14 Oct 2022 15:07:17 GMT

Thanks so much Anonymous. It was my false again that I didn't explane in detail. I am looking for the min and max of the sum for each day.

Year	Month	Day	Amount	Sum for each Day	Min	Max
					$ 1,517.11	$ 2,713.94
2022	August	21	$ 11.20
2022	August	21	$ 87.29
2022	August	21	$ 922.22
2022	August	21	$ 130.95	$ 1,151.66
2022	August	23	$ 927.71
2022	August	23	$ 248.29
2022	August	23	$ 293.22
2022	August	23	$ 844.01	$ 2,313.22
2022	August	25	$ 591.88
2022	August	25	$ 964.07
2022	August	25	$ 81.85
2022	August	25	$ 826.76	$ 2,464.55
2022	August	27	$ 992.91
2022	August	27	$ 787.31
2022	August	27	$ 933.72	$ 2,713.94
2022	August	29	$ 334.67
2022	August	29	$ 778.57
2022	August	29	$ 678.65
2022	August	29	$ 500.31	$ 2,292.20
2022	August	31	$ 101.28
2022	August	31	$ 473.95
2022	August	31	$ 941.87	$ 1,517.11

Re: How to get Max and Min Amount by Month

danyeungw — Fri, 14 Oct 2022 15:13:40 GMT

Correction: Min is 1151.60

Re: How to get Max and Min Amount by Month

Anonymous — Mon, 17 Oct 2022 01:01:52 GMT

Hi danyeungw ,

I updated the sample pbix file(see the attachment) base on your latest info, please check if that is what you want...

Sum of Amount = SUM('Table'[Amount])

Min = MINX(GROUPBY(ALLSELECTED('Table'),'Table'[Year],'Table'[Month],'Table'[Day]),[Sum of Amount])

Max = MAXX(GROUPBY(ALLSELECTED('Table'),'Table'[Year],'Table'[Month],'Table'[Day]),[Sum of Amount])

Best Regards

Re: How to get Max and Min Amount by Month

danyeungw — Mon, 17 Oct 2022 14:51:04 GMT

Anonymous Thank you so much for your patience. It works great.