I am expecting to get average of the summation of count by month

Question

I have a table like this

Date	count
1/1/2021	1
1/2/2021	1
1/2/2021	1
2/2/2022	1
2/2/2022	1
2/3/2022	1

I want to get yearly avg based on monthly sum using dax

Year	mmonth	sumofmonth	avgper month by yr
2021	1	1	1.5
2021	2	2	1.5
2022	2	2	1.5
2022	3	1	1.5

I am expecting to get average of the summation of count by month

Answer 1

First populate Month, Year columns using Power Query. Then use Group BY and populate SumOfMonth column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUUrOL80rUYrViVYy1DfUNzIwMgQKGkIFjAgIGEEEjPAJGCMJxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"count", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Date.2", "Date.1", "Date.3", "count"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Date.2", type text}, {"Date.1", type text}, {"Date.3", type text}}, "en-US"),{"Date.2", "Date.1", "Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type3", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Month"}, {{"SumofMonth", each List.Sum([count]), type nullable number}})
in
    #"Grouped Rows"

Then using DAX, write following measure

Measure = CALCULATE(AVERAGE('Table'[SumofMonth]), ALLEXCEPT('Table', 'Table'[Year]))