Forum Discussion

Pio_Mat's avatar
Pio_Mat
New Member
3 years ago
Solved

Calculated table from two tables: aggregated plus detailed

Hi,

please anyone help me to solve following issue.

 

I have two tables (see also screens):

  • One, SALES_PREV_MOTHS, with aggregated monthly, historical sales data per customer.
    This table is quite large and I do not want it to be refreshed.
  • Second table, SALES_CURRENT_MNTH, with daily sales figures per customer.
    This table is refreshed on daily basis.

Expected outcom is to have a third table, SALES_TOTAL, with aggregated monthly sales per customer, where current month's figures will adjust when SALES_CURRENT_MNTH table is refreshed.

 

Thanks for an advice! 🙂

 

 

Need Help
  • gmsamborn's avatar
    gmsamborn
    3 years ago

    Hi Pio_Mat 

    Would something like this help?

    _SummaryTable = 
VAR _Table =
    SUMMARIZE(
        'SALES_CURR_MTH',
        'SALES_CURR_MTH'[CustomerID],
        "Year-month",
            VAR _Dt = MIN( 'SALES_CURR_MTH'[Date] )
            VAR _Yr = YEAR( _Dt )
            VAR _Mon = MONTH( _Dt )
            RETURN
                DATE( _Yr, _Mon, 1),
        "Sales",
            SUM( 'SALES_CURR_MTH'[Sales] )
    )
RETURN
    UNION(
        'SALES_PREV_MTHS',
        SELECTCOLUMNS(
            _Table,
            "Year-month",
                [Year-month],
            "CustomerID",
                [CustomerID],
            "Sales",
                [Sales]
        )
    )

4 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Pio_Mat in two tables, according to picture, there are different grain of granularity of data: mix monthly and daily level. My personal experience: the best is to have it on single level like on daily as it is easier to aggregate it on monthly / yearly level...

    When you analyse your possibilities and next steps please check link how to do as possible example in Power Query on link below. Depending on your analysis maybe some other type of query would be needed in Power Query, but most probably Power Query is your best option.

    https://learn.microsoft.com/en-us/power-query/append-queries 

    Did I answer correctly? Kudos appreciate / accept solution.

    • Pio_Mat's avatar
      Pio_Mat
      New Member
      3 years ago

      Thank you, some_bih !

      My idea was more like to find a way to calculate cumulated current month's sales per customer from a table SALES_CURR_MTH and then, having data with the same granularity as in SALES_PREV_MTHS, union both?

      My intuition says it should be doable but I am to new in DAX to find a way how to do that 🙂

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    3 years ago

    Hi Pio_Mat 

    Would something like this help?

    _SummaryTable = 
VAR _Table =
    SUMMARIZE(
        'SALES_CURR_MTH',
        'SALES_CURR_MTH'[CustomerID],
        "Year-month",
            VAR _Dt = MIN( 'SALES_CURR_MTH'[Date] )
            VAR _Yr = YEAR( _Dt )
            VAR _Mon = MONTH( _Dt )
            RETURN
                DATE( _Yr, _Mon, 1),
        "Sales",
            SUM( 'SALES_CURR_MTH'[Sales] )
    )
RETURN
    UNION(
        'SALES_PREV_MTHS',
        SELECTCOLUMNS(
            _Table,
            "Year-month",
                [Year-month],
            "CustomerID",
                [CustomerID],
            "Sales",
                [Sales]
        )
    )
    • Pio_Mat's avatar
      Pio_Mat
      New Member
      3 years ago

      Thank you, gmsamborn ,

      after some small adjustments to my 'real' environment I got exactly what I was looking for.

      Thank you so much!

      🙂