Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
TeK
Regular Visitor

Monthly sales report with YoY comparison with different start dates

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

CustomerStart Date
A3/15/2024
B2/9/2024
C2/23/2024
D2/1/2024

 

Sales amounts table

CustomerTransaction NoSales DateAmount
ATransaction 13/8/2024300
ATransaction 23/22/2024500
ATransaction 33/22/2023150
BTransaction 42/16/2024400
BTransaction 52/2/2024200
BTransaction 62/2/2023500
CTransaction 73/1/2024500
CTransaction 82/1/2023600
DTransaction 93/1/2024340


Desired Output:

Customer202402202403
A0350
B0400
C0500
D0340

 

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.

 

 

 

1 ACCEPTED SOLUTION
johnbasha33
Solution Sage
Solution Sage

  

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 !!

          3.  

@TeK

View solution in original post

15 REPLIES 15
dufoq3
Super User
Super User

Hi @TeK, what about this?

 

Result:

dufoq3_0-1713082972594.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

TeK
Regular Visitor

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?

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

TeK
Regular Visitor

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:

 

Customer202402202403
A0350
B0400
C0500
D0340

OK, but where did such dates come from?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

TeK
Regular Visitor

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

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

 

dufoq3_0-1713189434161.png

 

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

johnbasha33
Solution Sage
Solution Sage

  

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 !!

          3.  

@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

TeK_0-1712782375468.png

 

@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.

@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.

@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:

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:

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])

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors