Monthly sales report with YoY comparison with different start dates

[TeK]

I would like to create a report in customer level to measure the sales amounts after a certain start date (re-signing date) for existing customers. There should a comparison aspect to measure growth vs. 12 months ago data. I have worked on several files for days but finally I am admiting that my knowledge is not enough. Here is what I need:

- Report should be in customer level and monthly

- The amount after the start date should be considered only, earlier times hould be shown as zero

- There needs to be a monthly comparison for amounts betweeen this year vs 12 months ago and if the difference should be calculated. If the difference is negative then it should be shown as zero. Dates should be considered as date

Start Dates table

Customer	Start Date
A	3/15/2024
B	2/9/2024
C	2/23/2024
D	2/1/2024

Sales amounts table

Customer	Transaction No	Sales Date	Amount
A	Transaction 1	3/8/2024	300
A	Transaction 2	3/22/2024	500
A	Transaction 3	3/22/2023	150
B	Transaction 4	2/16/2024	400
B	Transaction 5	2/2/2024	200
B	Transaction 6	2/2/2023	500
C	Transaction 7	3/1/2024	500
C	Transaction 8	2/1/2023	600
D	Transaction 9	3/1/2024	340

Desired Output:

Customer	202402	202403
A	0	350
B	0	400
C	0	500
D	0	340

Explanation of calculation

A: Transaction 1 (8th of March) is earlier than start date (15th of Mar), should not be counted. Transaction 2 should be counted as it is later than start date. Then comparing with 12 months ago, transaction 3 should be deducted.

B: Transaction 5 is earlier than start date, it should not be counted, Transaction 4 (16th Feb) should be counted as it is later than start date (9th of Feb). Then there will be no reduction as 12 months ago amount is before the strat dates 12 months ago.

C: Transaction 7 (1st of Mar) is after start date (23rd of Feb) so it will be counted, no amount to be deducted as in the month of 12 months ago (Mar 2023) there is no amount. February is zero as there is no volume in Feb 2024 , 12 months data should be deducted for that scenario

😧 Transaction 9: 1st of March is after start date (1st of Feb) so it will count, no amount for 12 months ago that would be deducted

I would be glad if you can help me on that.

[dufoq3]

Hi @TeK, what about this?

Result:

let
    Table_StartDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLWNzTVNzIwMlGK1YlWcgKKGOlbIgScwQJGxggRF7CIIVQgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Start Date" = _t]),
    Table_SalesAmount = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQopSswrTkwuyczPUzAE8o31LfSNDIxMQEwDA6VYHUxlRmBlRkYwdaY41BkjqQOxDU0h6pzQ1IHMMNI3NIOZZ2KAXZ0pWB3cWiMcyswQyoyRXOeMpswc7DpDdE+gK7OAOA5mmhlUmQuaMktU04xNgMpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Transaction No" = _t, #"Sales Date" = _t, Amount = _t]),
    ChangedType_StartDates = Table.TransformColumnTypes(Table_StartDates,{{"Start Date", type date}}, "en-US"),
    ChangedType_SalesAmount = Table.TransformColumnTypes(Table_SalesAmount,{{"Sales Date", type date}, {"Amount", type number}}, "en-US"),
    MergedQueries = Table.NestedJoin(ChangedType_SalesAmount, {"Customer"}, ChangedType_StartDates, {"Customer"}, "Table_StartDates", JoinKind.LeftOuter),
    ExpandedTable_StartDates = Table.ExpandTableColumn(MergedQueries, "Table_StartDates", {"Start Date"}, {"Start Date"}),
    Ad_SalesYear = Table.AddColumn(ExpandedTable_StartDates, "Sales Year", each Date.Year([Sales Date]), Int64.Type),
    GroupedRows = Table.Group(Ad_SalesYear, {"Customer"}, {{"Difference", each 
        [ a = List.Max([Start Date]), //Max [Start Date]
          b = List.Sum(Table.SelectRows(_, (x)=> x[Sales Date] >= a)[Amount]), //Sum [Amount] >= [Start Date]
          c = List.Sum(Table.SelectRows(_, (x)=> x[Sales Date] >= Date.AddYears(a, -1) and x[Sales Year] < Date.Year(a))[Amount]), //Sum [Amount] previous year but >= [Start Date] previous year
          d = b - (c ?? 0)
        ][d], type number}})
in
    GroupedRows

[TeK]

Thank you very much! That's great and that worked, only thing I can ask for support is how I can see the monthly figures of that total table. Is there a way to add that so that I can add it into my filters to change the views in monthly?

[dufoq3]

Could you be more specific please? Provide sample data and expected result please.

[TeK]

For Februrary as an example it should be showing as 202402 and each of the months should be shown as I will be using that for a bigger base to see the results in each month.

Desired Output:

Customer	202402	202403
A	0	350
B	0	400
C	0	500
D	0	340

[dufoq3]

OK, but where did such dates come from?

[TeK]

These are from Sales Date from Sales Amount table. Sales date should be changed into Year & Month like 202302 

[dufoq3]

Why do you have in your expected results 0 for 202402?

Provide more complex data please - with at least 3 months. Don't forget expected result.

[johnbasha33]

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[johnbasha33 · Accepted solution]

To achieve the desired output in Power Query, you can follow these steps:

1. Load both tables into Power Query.
2. Merge the tables based on the "Customer" column.
3. Calculate the difference between the "Sales Date" and "Start Date" to identify if the sales occurred after the start date.
4. Filter out the sales that occurred before the start date.
5. Group the data by customer and month, summing the sales amounts.
6. Pivot the data to have the months as columns.
7. Calculate the comparison between the current month and the same month 12 months ago.
8. Adjust the calculation to handle cases where there's no data 12 months ago.

   Here's the step-by-step implementation in Power Query:

   1. Load both tables into Power Query.
   2. Merge the tables based on the "Customer" column.
   3. Add a custom column to calculate the difference between "Sales Date" and "Start Date":
      = Duration.Days([Sales Date] - [Start Date])

      1. Filter out the sales that occurred after the start date:
         
         

         = if [DaysSinceStart] >= 0 then "Yes" else "No"

         1. Group the data by customer and month, summing the sales amounts:
            
            

            = Table.Group(#"Filtered Rows", {"Customer", Date.Year([Sales Date]), Date.Month([Sales Date])}, {{"SalesAmount", each List.Sum([Amount]), type number}})

            1. Pivot the data to have the months as columns.

            2. Add a custom column to calculate the comparison with 12 months ago:

               = try [SalesAmount] - List.First(List.Select(PreviousMonthSales, each [Year] = Date.Year([Sales Date]) - 1 and [Month] = Date.Month([Sales Date]) - 1))[SalesAmount] otherwise [SalesAmount]

               1. Adjust the calculation to handle cases where there's no data 12 months ago:
                  = try [SalesAmount] - List.First(List.Select(PreviousMonthSales, each [Year] = Date.Year([Sales Date]) - 1 and [Month] = Date.Month([Sales Date]) - 1))[SalesAmount] otherwise [SalesAmount] - [SalesAmount]

                  This process should give you the desired output with the comparisons between the current month and the same month 12 months ago, handling cases where there's no data 12 months ago.

                  
                  Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

@TeK

[TeK]

@johnbasha33 I now realized that most of the lines that you refer to needs to be added in power query and it gave me a new dimension. Now I came almost to the last lines.

On the last line I am getting the error message of "Expression.Error: The name 'PreviousMonthSales' wasn't recognized. Make sure it's spelled correctly." How should I define PreviousMonthSales as? If I write that as "PreviousMonthSales" then I am getting the cells as error. Can you please help on that?

= try [SalesAmount] - List.First(List.Select(PreviousMonthSales, each [Year] = Date.Year([Sales Date]) - 1 and [Month] = Date.Month([Sales Date]) - 1))[SalesAmount] otherwise [SalesAmount] - [SalesAmount]

This is how it shows at the moment

[johnbasha33]

@TeK 

Apologies for any confusion. The "previous month sales columns" refer to the columns containing sales data from the same months in the previous year. In the context of the provided steps, after pivoting the data to have months as columns, you'll have columns representing sales for each month. The step to calculate the comparison with 12 months ago involves accessing the sales data from the corresponding month in the previous year and calculating the difference between the current month's sales and the sales from the same month 12 months ago.

For example, if you have columns representing sales for January, February, March, etc., the "previous month sales columns" would be the columns representing sales for January of the previous year, February of the previous year, March of the previous year, and so on. The calculation then compares the sales for the current month with the sales from the corresponding month 12 months ago.

[TeK]

@johnbasha33 I have used your formulas up to Previous month sales, then I have created columns with some basic if formulas whether these should be included or not and sum it up in visuals. Many thanks! I do have a new issue regarding not getting the sums but I will create a new topic for that one.

[TeK]

@johnbasha33 

Thank you very much, how should I define Previous month sales? I guess this should be between two steps but I was not able to find how to do that.

1. Pivot the data to have the months as columns.

2. Add a custom column to calculate the comparison with 12 months ago:

[TeK]

Thank you very much, how should I define Previous month sales? I guess this should be between two steps but I was not able to find how to do that.

1. Pivot the data to have the months as columns.

2. Add a custom column to calculate the comparison with 12 months ago:

[TeK]

First of all many thanks for your reply and working on it. It might be a simple question but while I am adding the below to new column, I am not getting the start date. I merged the two tables based on customer but cannot see that in the formula. Can it be regarding relations or I chose wrong merge option like merging two tables 

= Duration.Days([Sales Date] - [Start Date])