topic Re: summarize a table in a table in DAX Commands and Tips

summarize a table in a table

Anonymous — Wed, 14 Jun 2023 13:25:58 GMT

Hi,

I have created a table that shows each day of a subscription with the daily net amount.

Example: Invoice for the period 05/14/2022 - 05/13/2023. Total amount $1,200.

The code below generates for each day, the daily amount.
14.05.2022 = $3,28
15.05.2022 = $3,28
16.05.2022 = $3,28 ...

Table_summary =
SUMMARIZE (
    CALCULATETABLE (
        SELECTCOLUMNS (
            GENERATE (
                CALCULATETABLE (
                    SELECTCOLUMNS (
                        table1,
                        "Invoicenumber1", table1[invoicenumber],
                        "Startdate1", table1[BILLDATE_cred],
                        "Enddate1", table1[BILLDATE_cred],
                        "Netamount_daily1", table1[Netamount_total]
                    )
                ),
                CALENDAR ( [Startdate1], [Enddate1] )
            ),
             "Invoicenumber", [Invoicenumber1],
            "Netamount_daily", [Netamount_daily1],
            "Date", [Date],
            "Startdate", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )
    ),
    [Date],
    [Startdate],
    [ORDNO],
    [Netamount_day]
)

Now I have multiple invoices and want to combine the amount per month. Startdate is always the first of a month. That means it should be summarized over Startdate.

I only managed to do this by creating a new table and using SUMMARIZECOLUMNS. But then I created two tables and millions of rows from table1 using only memory. I tried GROUPBY, but nothing changes.

Table_summary =
GROUPBY (
    SUMMARIZE (
        CALCULATETABLE (
            SELECTCOLUMNS (
                GENERATE (
                    CALCULATETABLE (
                        SELECTCOLUMNS (
                            table1,
                            "Invoicenumber1", table1[invoicenumber],
                            "Startdate1", table1[BILLDATE_cred],
                            "Enddate1", table1[BILLDATE_cred],
                            "Netamount_daily1", table1[Netamount_total]
                        )
                    ),
                    CALENDAR ( [Startdate1], [Enddate1] )
                ),
                "Invoicenumber", [Invoicenumber1],
                "Netamount_daily", [Netamount_daily1],
                "Date", [Date],
                "Startdate", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
            )
        ),
        [Date],
        [Startdate],
        [Invoicenumber],
        [Netamount_daily]
    ),
    [Netamount_daily]
)

Is a further summary of this table possible at all?

Thanks

Re: summarize a table in a table

johnt75 — Wed, 14 Jun 2023 14:07:28 GMT

I don't think that you need the outer SUMMARIZE or CALCULATETABLE. Try

Table_summary = GROUPBY ( SELECTCOLUMNS ( GENERATE ( CALCULATETABLE ( SELECTCOLUMNS ( table1, "Invoicenumber1", table1[invoicenumber], "Startdate1", table1[BILLDATE_cred], "Enddate1", table1[BILLDATE_cred], "Netamount_daily1", table1[Netamount_total] ) ), CALENDAR ( [Startdate1], [Enddate1] ) ), "Invoicenumber", [Invoicenumber1], "Netamount_daily", [Netamount_daily1], "Date", [Date], "Startdate", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) ), [Startdate], "Net daily total", SUMX ( CURRENTGROUP (), [Netamount_daily] ) )

Re: summarize a table in a table

tamerj1 — Wed, 14 Jun 2023 14:21:29 GMT

Hi Anonymous
These are the same two columns. Please clarify

"Startdate1", table1[BILLDATE_cred],
"Enddate1", table1[BILLDATE_cred],

Re: summarize a table in a table

Anonymous — Wed, 14 Jun 2023 14:32:20 GMT

Hi joint75,

that's really awesome. It works and saved me a lot of memory. thanks so much 🙂

Re: summarize a table in a table

Anonymous — Wed, 14 Jun 2023 14:36:07 GMT

Hi tamerj1,

thanks for your help and your attentive eye. That is correct. For that table start and enddate is the same. But I have other tables where they are different. I can use joint75 suggestion for them.

Thank you 🙂

Re: summarize a table in a table

tamerj1 — Wed, 14 Jun 2023 14:47:06 GMT

Anonymous
Thank you. You may also try

Table_summary = GROUPBY ( ADDCOLUMNS ( GENERATE ( SUMMARIZE ( table1, table1[invoicenumber], table1[BILLDATE_cred1], table1[BILLDATE_cred2], "Netamount_daily", SUM ( table1[Netamount_total] ) ), CALENDAR ( table1[BILLDATE_cred1], table1[BILLDATE_cred2] ) ), "Startdate", EOMONTH ( [Date], 0 ) ), [Date], [Startdate], [Invoicenumber], "Netamount_daily", SUMX ( CURRENTGROUP (), [Netamount_daily] ) )

Re: summarize a table in a table

Anonymous — Wed, 14 Jun 2023 14:57:16 GMT

Oh, that works great too. thank you for your time and help. Always appreciate your help :3

Re: summarize a table in a table

tamerj1 — Wed, 14 Jun 2023 15:01:08 GMT

Anonymous

Only amend the following

EOMONTH ( [Date], -1 ) + 1

Re: summarize a table in a table

Anonymous — Wed, 14 Jun 2023 15:08:06 GMT

improved is improved 😊 thanks