Forum Discussion

Gene_NZ's avatar
Gene_NZ
Frequent Visitor
1 year ago
Solved

Grouping Weekly Sales by Calendar Month

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 WeekSales
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's avatar
    Irwan
    1 year ago

    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.

6 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Gene_NZ
      Frequent Visitor
      1 year ago

      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.

    • Gene_NZ's avatar
      Gene_NZ
      Frequent Visitor
      1 year ago

      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's avatar
        Irwan
        Icon for Super User rankSuper User
        1 year ago

        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.