cancel
Showing results 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.

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:

 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'[Date] < DATE ( YEAR ( TODAY () )MONTH ( TODAY () )1 )
),
)
)
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

2 REPLIES 2
Community Support

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 (
FILTER (
'ADJ2'[Date] < DATE ( CurrentYear, TodaysMonth, 1 )
)
)
VAR FutureDPLNSum = CALCULATE (
SUM ( 'DPLN'[DPLN Qty] ),
FILTER (
'DPLN',
'DPLN'[Date] >= DATE ( CurrentYear, TodaysMonth, 1 )
)
)
RETURN

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.

New Member

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors