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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kolumam
Post Prodigy
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 ? 

CODdd.JPG

1 ACCEPTED SOLUTION

@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.

View solution in original post

8 REPLIES 8
rocky09
Solution Sage
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.

Capture.JPG

 

 

 

Hi

 

Cumulative is showing wrong values. Please check below.

 

 

 

das.JPG

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

@Kolumam
Try this formula:

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

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

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

@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

Please try my custom visuals

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 ?

ddd.JPG

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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