Calculated table from two tables: aggregated plus detailed

[Pio_Mat]

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

[gmsamborn · Accepted solution]

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]

Thank you, @gmsamborn ,

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

Thank you so much!

🙂

[some_bih]

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]

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 🙂