Monthly/Running Total by Month in calculated table

[datadmin-austin]

Hello all,

I am trying to create a calculated table off my data to get monthly totals and rolling totals. This sample would be the output:

Date	Monthly 1	Rolling 1	Monthly 2	Rolling 2	Monthly Total	Rolling Total
12/1/2023	$                107,841.54	$             1,294,098.46	$                 165,805.06	$             1,989,660.73	$  273,646.60	$    3,283,759.19
1/1/2024	$                109,892.54	$                109,892.54	$                 181,075.82	$                 181,075.82	$  290,968.36	$        290,968.36
2/1/2024	$                111,534.00	$                221,426.54	$                 181,879.15	$                 362,954.97	$  293,413.15	$        584,381.51

I have gotten the monthly totals to work with this DAX:

Monthly Revenue Data = 
VAR MonthSummary =
    SUMMARIZE(
        ADDCOLUMNS(
            'Combine Files',
            "MonthStartDate", DATE(YEAR('Combine Files'[Invoice Date]), MONTH('Combine Files'[Invoice Date]), 1)
        ),
        [MonthStartDate],
        "Monthly1", 
            CALCULATE(
                SUMX(
                    'Combine Files',
                    'Combine Files'[Price] * 'Combine Files'[Quantity]
                ),
                'Combine Files'[Item Number] = 10
            ),
        "Monthly2",
            CALCULATE(
                SUMX(
                    'Combine Files',
                    'Combine Files'[Price] * 'Combine Files'[Quantity]
                ),
                'Combine Files'[Item Number] = 30
            )
    )

VAR MonthlyRevenueData =
    ADDCOLUMNS(
        MonthSummary,
        "Total",
            [Monthly1] + [Monthly2]
    )

RETURN
    SELECTCOLUMNS(
        MonthlyRevenueData,
        "Date", [MonthStartDate],
        "Monthly1", [Monthly1],
        "Monthly2", [Monthly2],
        "Total", [Total]
    )

But I am struggling to get the running total that is also exculsive of other years meaning it is showning the cumulative of months from the current year and would reset Jan 1 for that year.

Can any assist in this?

[VahidDM]

Hey @datadmin-austin 

Try this (I couldn't test because of lack of sample data)

MonthlyRevenueData = 
VAR MonthSummary =
    SUMMARIZE(
        ADDCOLUMNS(
            'Combine Files',
            "MonthStartDate", DATE(YEAR('Combine Files'[Invoice Date]), MONTH('Combine Files'[Invoice Date]), 1)
        ),
        [MonthStartDate],
        "Monthly1", 
            CALCULATE(
                SUMX(
                    'Combine Files',
                    'Combine Files'[Price] * 'Combine Files'[Quantity]
                ),
                'Combine Files'[Item Number] = 10
            ),
        "Monthly2",
            CALCULATE(
                SUMX(
                    'Combine Files',
                    'Combine Files'[Price] * 'Combine Files'[Quantity]
                ),
                'Combine Files'[Item Number] = 30
            )
    )

VAR MonthlyRevenueData =
    ADDCOLUMNS(
        MonthSummary,
        "Total", [Monthly1] + [Monthly2]
    )

RETURN
    ADDCOLUMNS(
        MonthlyRevenueData,
        "Rolling1",
            CALCULATE(
                SUMX(
                    'Combine Files',
                    'Combine Files'[Price] * 'Combine Files'[Quantity]
                ),
                'Combine Files'[Item Number] = 10,
                FILTER(
                    ALL('Combine Files'),
                    'Combine Files'[Invoice Date] <= EARLIER([MonthStartDate]) &&
                    YEAR('Combine Files'[Invoice Date]) = YEAR(EARLIER([MonthStartDate]))
                )
            ),
        "Rolling2",
            CALCULATE(
                SUMX(
                    'Combine Files',
                    'Combine Files'[Price] * 'Combine Files'[Quantity]
                ),
                'Combine Files'[Item Number] = 30,
                FILTER(
                    ALL('Combine Files'),
                    'Combine Files'[Invoice Date] <= EARLIER([MonthStartDate]) &&
                    YEAR('Combine Files'[Invoice Date]) = YEAR(EARLIER([MonthStartDate]))
                )
            ),
        "RollingTotal",
            CALCULATE(
                SUMX(
                    'Combine Files',
                    'Combine Files'[Price] * 'Combine Files'[Quantity]
                ),
                FILTER(
                    ALL('Combine Files'),
                    'Combine Files'[Invoice Date] <= EARLIER([MonthStartDate]) &&
                    YEAR('Combine Files'[Invoice Date]) = YEAR(EARLIER([MonthStartDate]))
                )
            )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[datadmin-austin]

@VahidDM  Thank you very much!! Would there be any reason within the expression why the "Monthly1" and "Rolling1" are not the same value for Jan 1? I added the sample data output below.

I think it may be because not all invoices occur on the 1st of the month, so I would need to get everything from the matching month.

MonthStartDate	Monthly1	Monthly2	Total	Rolling1	Rolling2	RollingTotal
1/1/2024	197,674.44	104,929.06	302,603.50	196,927.33	102,404.97	326,732.75
2/1/2024	198,551.41	106,496.38	305,047.79	394,988.82	209,069.39	651,723.21