Grouping Weekly Sales by Calendar Month

[Gene_NZ]

Hi,

I am trying to come up with a solution to group weekly sales by calendar month.  My Sales table contains End of week dates and sales.

I have a date table that links to the sales table (Date>End of Week) that contains date, end of week, end of month.

In the sales table below, there are weeks that crossed over two months.  I'm not sure if it is possible to group the sales below into calendar months.  E.g. Sales for weekending 4th Feb will be distributed across 2 months. January( sales/7 * 3) and February(sales/7*4).

Any helpful tips will be appreciated.  Thank you.

Sales Table:

End of Week	Sales
Sunday, 7 January 2024	$6,803,326
Sunday, 14 January 2024	$7,627,859
Sunday, 21 January 2024	$7,791,092
Sunday, 28 January 2024	$7,408,869
Sunday, 4 February 2024	$7,995,371
Sunday, 11 February 2024	$7,909,155
Sunday, 18 February 2024	$7,745,227
Sunday, 25 February 2024	$7,756,536
Sunday, 3 March 2024	$7,761,117
Sunday, 10 March 2024	$7,515,989
Sunday, 17 March 2024	$7,383,671
Sunday, 24 March 2024	$7,558,089
Sunday, 31 March 2024	$7,022,033
Sunday, 7 April 2024	$7,176,215
Sunday, 14 April 2024	$7,256,546
Sunday, 21 April 2024	$7,404,089
Sunday, 28 April 2024	$6,751,174
Sunday, 5 May 2024	$7,432,954

[Irwan]

hello @Gene_NZ 

i might be misunderstood, but what condition does make January is multiplied by 3 and February is by 4?

and what is expected result from your sample above?

Thank you.

[Gene_NZ]

Hi Irwan,

Please see below:   The difference between the two totals is sales for May 2024

Sales Table
End of Week	Sales
Sunday, 7 January 2024	$           6,803,326
Sunday, 14 January 2024	$           7,627,859
Sunday, 21 January 2024	$           7,791,092
Sunday, 28 January 2024	$           7,408,869
Sunday, 4 February 2024	$           7,995,371
Sunday, 11 February 2024	$           7,909,155
Sunday, 18 February 2024	$           7,745,227
Sunday, 25 February 2024	$           7,756,536
Sunday, 3 March 2024	$           7,761,117
Sunday, 10 March 2024	$           7,515,989
Sunday, 17 March 2024	$           7,383,671
Sunday, 24 March 2024	$           7,558,089
Sunday, 31 March 2024	$           7,022,033
Sunday, 7 April 2024	$           7,176,215
Sunday, 14 April 2024	$           7,256,546
Sunday, 21 April 2024	$           7,404,089
Sunday, 28 April 2024	$           6,751,174
Sunday, 5 May 2024	$           7,432,954
TOTAL	$      134,299,312
Expected Results:
Month	Sales
Jan-24	$         33,057,734
Feb-24	$         32,414,625
Mar-24	$         32,805,975
Apr-24	$         30,711,725
TOTAL	$      128,990,059

[Irwan · Accepted solution]

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.

[Gene_NZ]

Thank you @Irwan , this approach surely helps. Highly appreciated.

[Irwan]

hello @Gene_NZ 

glad to be a help.

Thank you.

[Gene_NZ]

Hi Irwan,

Week ending 04 Feb 2024 cointains days that fall into two months.  29 Jan to 31 Jan  (3 days) should be allocated to January and 01 Feb to 04  (4 days)Feb should be February allocation.  Expected results should be sales grouped into Calendar months.  Example: week ending 04 Feb 2024 sales should be split into January and February based on the actual calendar dates of the week.   Hope this makes sense.