Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Combining data from 2 tables with different date granularity

Hello,
I want to calculate the weekly rotation (=rate of sales) of products sold per store. 

To do that, I have two tables:

- Total sales of specific products at a particular retailer. Each entry has: SAP code, name of retailer, quantity sold, and a date. Granularity here is either monthly (i.e. quantity sold in 1 whole month), or weekly

- Distribution: that is the number of stores (point of sales) in which each product is present. Each entry has: SAP code, name of retailer, number of stores where product is available, and a date.
Granularity here is quarterly.

Weekly rotation should be calculated as: [sum products sold]/[distribution]. So it's the average sales per store.
But I cannot think of way to align the different granularity of date. Can someone help?

Cheers


Need Help

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Quarter, Month name and Month number.  Sort the Month name column by the Month number.  To your visual, drag Year and Quarter from the calendar Table.  Write these measures:

    Qty sold = sum('Sales'[Quantity sold])

    Dist = sum('Distribution'[Quantity])

    Hope this helps.

     

  • lucadelicio's avatar
    lucadelicio
    Impactful Individual
    3 years ago

    It's more easier to help you if you upload your pbix.
    Also image of the relationship and the image of the table.
    You can try to report the single data in every table with a calculated column.
    Then create a data table (with all the day of all the year present in the other table).
    Then use it like a slicer to select and filter the divide measure to calculate the result.

    I hope I have been helpful.
    Mark it as a solution if resolve your problem.

    Ciao!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks for your suggestions lucadelicio and Ashish_Mathur 
    The model contains my company's proprietary data, so difficult to share the pbix. But here's where I am at the moment.

    There's already a customized date table, including nr of week, month, year, etc.

    I have merged the queries "sales" and "distribution" in a new query, with left outer. Because "sales" has the finest date granularity. Matching with "Country", "Date", "SAP" (the product ID), and "Retailer". "CU" is the quatity sold.

    Now I need to copy the "Distribution.POS" value to all the rows with "null" in the future until there's a new value, matching with "Country", "SAP", and "Retailer". So I believe it's about creating a new column with a conditional formula. Can you help me with the formula?
    Big thanks in advance.

    • lucadelicio's avatar
      lucadelicio
      Impactful Individual
      3 years ago

      In power query transform data add a conditional column.

      If Column equals null then value else Column
      This is the M code:
      = Table.AddColumn(#"Changed Type", "Custom", each if [Column5] = null then 156 else [Column5])

      Hope i help you.

      Mark it as a solution if you resolve your problem.

      Ciao!