Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pbilover1234
New Member

Calculate aggregate sum for column by year?

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: 

MonthYearDateQuantity
Jan2024Jan 1, 2024180000
Mar2024Mar 1, 202445000
Apr2024Apr 1, 202490000

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

 

pbilover1234_0-1708635372373.png

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

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

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.

 

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.