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.

Post Prodigy

## Need help in formula

Hi  All,

I need a cumulative sum of size for each month from the start July 2016 to August 2018. How do I achieve this using DAX ?

1 ACCEPTED SOLUTION
Employee

@Kolumam,

Create a calendar table in Power BI Desktop following the guide in this blog, create relationship between the date field of Date table and COD field of Project table. Then change your DAX to the following, drag date field of Date table and the measure to your chart.

```Running Totals =
CALCULATE (
SUM ( Project[Operating Size] ),
FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)```

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Solution Sage

@Kolumam

```Running Totals =
CALCULATE (
SUM ( 'Table1'[Size(MWP)] ),
ALL ( 'Table1' ),
'Table1'[COD] <= EARLIER ( 'Table1'[COD] )
)```

try this, but, you need to fix the date column as I mentioned in your other thread.

Post Prodigy

Hi

Cumulative is showing wrong values. Please check below.

Solution Sage

Can you post sample data? (copy directly from excel to here). I can test it on my system.

Resolver III

@Kolumam
Try this formula:

Running Totals =
CALCULATE (
SUM ( 'Table1'[Size(MWP)] ),
FILTER (
ALLEXCEPT ( 'Table1', 'Table'[COD] ),
'Table1'[COD] <= MAX ( 'Table1'[COD] )
)
)

Resolver III

Sorry, formula doesn't correct. Try this formula:

Running Totals =
CALCULATE (
SUM ( 'Table1'[Size(MWP)] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[COD] <= MAX ( 'Table1'[COD] ) )
)

Community Champion

@Kolumam

Another way could be to use INONORAFTER

```Measure =
VAR mymax =
MAX ( Table1[COD] )
RETURN
CALCULATE (
SUM ( Table1[Size(MWP)] ),
ISONORAFTER ( Table1[COD], mymax, DESC )
)```

Regards
Zubair

Post Prodigy

Hi All,

The issue with the below bar graph is that for the months with no COD it is not taking the same number and instead it is blank.

How do I fix this ?

Employee

@Kolumam,

Create a calendar table in Power BI Desktop following the guide in this blog, create relationship between the date field of Date table and COD field of Project table. Then change your DAX to the following, drag date field of Date table and the measure to your chart.

```Running Totals =
CALCULATE (
SUM ( Project[Operating Size] ),
FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)```

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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