Calculate aggregate sum for column by year?

[pbilover1234]

Hi all,

I have two tables, ADJ2 which contains values for quantity shipped, and DPLN which contains values for future months forecast. 

Both tables have the same format: 

Month	Year	Date	Quantity
Jan	2024	Jan 1, 2024	180000
Mar	2024	Mar 1, 2024	45000
Apr	2024	Apr 1, 2024	90000

I want to create a measure that shows total sum of ADJ2 and DPLN values for the year, depending on the month. I want to sum the values from the ADJ2 table for any months prior to today's month, and add that to the sum of DPLN values for future months (in the same year).

For example, if the current month was April, I'd want the measure to do this:

(Sum of ADJ2 values for Jan, Feb, Mar) + (Sum of DPLN values for Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec). 

I'm trying to show these values in a matrix with Month-Year as the columns. I can get the correct DPLN and ADJ2 values to populate, but they aren't summing as expected. 

This is the formula I'm using along with the results. 

MonthlyDifference =
VAR TodaysMonth =
    MONTH ( TODAY () )
RETURN
    CALCULATE (
        SUMX (
            FILTER (
                'ADJ2',
                'ADJ2'[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
            ),
            'ADJ2'[ADJ2 Qty]
        )
    )
        + CALCULATE (
            SUMX (
                FILTER (
                    'DPLN',
                    'DPLN'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
                ),
                'DPLN'[DPLN Qty]
            )
        )

For February, I'd expect to see 29,857,450 (ADJ2 value for Jan) + the sum of the values listed in the matrix for Feb thru December

[v-nuoc-msft]

Hi @pbilover1234 

Based on the description and formula you provided, it appears that you are doing the right thing, but are facing an issue where the expected amount is not being calculated as expected.

The problem may stem from the way the dates are compared or the way the data is structured in the table.

Validate date data types: Ensure that the "Date" column in the "ADJ2" and "DPLN" tables has the correct date format. If Power BI stores them as strings or other formats, they may not recognize them as dates, affecting filter criteria.

Consider simplifying measure for debugging purposes. For example, create a measure that sums "ADJ2" and another measure that sums "DPLN" and then add them together. As follows:

MonthlyDifference =
VAR TodaysMonth = MONTH ( TODAY() )
VAR CurrentYear = YEAR ( TODAY() )
VAR PastADJ2Sum = CALCULATE (
    SUM ( 'ADJ2'[ADJ2 Qty] ),
    FILTER (
        'ADJ2',
        'ADJ2'[Date] < DATE ( CurrentYear, TodaysMonth, 1 )
    )
)
VAR FutureDPLNSum = CALCULATE (
    SUM ( 'DPLN'[DPLN Qty] ),
    FILTER (
        'DPLN',
        'DPLN'[Date] >= DATE ( CurrentYear, TodaysMonth, 1 )
    )
)
RETURN
PastADJ2Sum + FutureDPLNSum

This breakdown ensures clarity and might help in pinpointing where the issue lies.

If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

Regards,

Nono Chen

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

[pbilover1234]

Hello @v-nuoc-msft 

I tried the suggested DAX and it returns the same values as what was in the screenshot. For months pror to today's month, it sums values only from the ADJ2 table, and for today's month and beyond, it only sums values from DPLN. I'm not sure how to get it to sum all vlaues in one measure