How to get Max and Min Amount by Month

[danyeungw]

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

[Anonymous]

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

[danyeungw]

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.

[Anonymous]

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

[danyeungw]

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

[danyeungw]

Correction: Min is 1151.60

[Anonymous · Accepted solution]

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

[danyeungw]

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

[danyeungw]

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

[Shaurya]

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: Forecast Period - Graphical Comparison

[Shaurya]

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: Forecast Period - Graphical Comparison