cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kolumam Member
Member

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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Need help in formula

@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 Senior Member
Senior Member

Re: Need help in formula

@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

 

 

 

Kolumam Member
Member

Re: Need help in formula

Hi

 

Cumulative is showing wrong values. Please check below.

 

 

 

das.JPG

rocky09 Senior Member
Senior Member

Re: Need help in formula

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

popov Member
Member

Re: Need help in formula

@Kolumam
Try this formula:

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

popov Member
Member

Re: Need help in formula

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

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

Super User
Super User

Re: Need help in formula

@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 )
    )
Kolumam Member
Member

Re: Need help in formula

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

Moderator v-yuezhe-msft
Moderator

Re: Need help in formula

@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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 225 members 1,967 guests
Please welcome our newest community members: