summarize a table in a table

[lost_flower]

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

[johnt75 · Accepted solution]

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

[lost_flower]

improved is improved 😊 thanks

[tamerj1]

Hi @lost_flower 
These are the same two columns. Please clarify

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

[lost_flower]

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 🙂

[tamerj1 · Accepted solution]

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

[lost_flower]

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

[tamerj1]

@lost_flower 

Only amend the following 

EOMONTH ( [Date], -1 ) + 1

[johnt75 · Accepted solution]

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

[lost_flower]

Hi joint75,

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