Grouping Weekly Sales by Calendar Month

hello Gene_NZ 

sorry for my late response, but please check if this accomodate your need. This solution needs a modification in Power Query.

1. in Power Query, create a custom column for 'Start of Week' then unpivot the table

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdK7agNBDAXQXxmWlCr01kyZxkUglUvjYvOABIIJCy7895lUtma2P1ykyz2dluP18rHeoER5WS/XdbsVRtYFlieHigLCvpzh7kgnGOAcUK0lyLQDoxFg4wzrDlSsUD0najl8vm0DbM1AgvKNtCuxAZllWfdkqAFz5CttV5qDSW5Iyuu6vX89KicgynmEEzMyaDX/TDExqQI+fMw6p1kFHNKEJobMgCKJRXn+3b5/HhSFA5ONSxgZ/9ehPu5gZIo6ndZXkJn3cnttoYlZfyDtRBiadXP+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End of Week" = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"End of Week", type date}, {"Sales", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "End of Week", "End of Week - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"End of Week - Copy", Int64.Type}}),
#"Subtracted from Column" = Table.TransformColumns(#"Changed Type1", {{"End of Week - Copy", each _ - 6, type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Subtracted from Column",{{"End of Week - Copy", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"End of Week - Copy", "Start of Week"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Sales"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Date"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","$","",Replacer.ReplaceText,{"Sales"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Sales"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value1",{{"Sales", Int64.Type}})
in
#"Changed Type3"

2. create a calculated column with following DAX for calculating sales in those weeks that have different month.

Total Sales = 
var _NumberofDate = 
IF(
    'Table'[Attribute]="Start of Week",
    DATEDIFF('Table'[Date],EOMONTH('Table'[Date],0),DAY)+1,
IF(
    'Table'[Attribute]="End of Week",
    DATEDIFF(EOMONTH('Table'[Date],-1)+1,'Table'[Date],DAY)+1
))
var _DateLeft = 
IF(
    _NumberofDate<7,
    _NumberofDate,
    0
)
Return
IF(
    _DateLeft>0,
    DIVIDE('Table'[Sales],7)*_DateLeft,
    'Table'[Sales]
)

 

3. create a new measure with following DAX for monthly sum value

Monthly Sales = 
var _Sum = 
SUMMARIZE(
    ADDCOLUMNS(
        'Table',
        "DateValue",
        FORMAT(EOMONTH('Table'[Date],0),"MMM-YY")
    ),
    'Table'[Total Sales],
    [DateValue]
)
Return
SUMX(
    _Sum,
    'Table'[Total Sales]
)

 

4. plot your values in table visual

 

kinda messy but hope you can get some light on how to achieve your goal.

Hope this will help.

Thank you.