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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MWitkin
Frequent Visitor

Cumulative Line Formula

Hello. I need to draw a cumulative line across this graph for both columns represented as bars in this graph visual:

pic1.jpg

Both columns are date columns and are on the same table, and the bars represent the count of the dates entered in the table in each month. The formula for this line is the same for both columns, and in plain text is pretty simple (Count of January dates, Count of January dates + Count of February dates, Count of January dates + Count of February dates + Count of March dates, and so on…).

 

 

I'm having trouble writing a DAX formula that would produce the desired result. I've tried quite a few of the formulas I've seen listed on this forum but have not had luck.

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

You should be able to use the formula that @Greg_Deckler provided and just change the SUM to a COUNT of [Planned] or [Actual].

 

 

Cummulative Actual Line =
CALCULATE (
    COUNTA ( 'Table1'[Actual] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

Cummulative Planned Line =
CALCULATE (
    COUNTA ( 'Table1'[Planned] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

 

Here is what I generated with your example data set.

Cummulative line.PNG

 

 

View solution in original post

11 REPLIES 11
Jeff_Aware
Regular Visitor

This keeps coming up first in Google for me, so I thought I'd leave this here for future users. the Power BI team have released the Quick Measures Preview, which among other things includes a quick calculation for running totals (as well as various Time Intelligence formulas like Year to Date)

sandrasanchez
Helper I
Helper I

Hi!

I have this DAX formula and it doesn't works for me, what am I doing wrong?

Thanks so much!!

 

Importe Acumulado = CALCULATE (
    SUM ( 'InvoiceSet'[Amount PreTax] );
    FILTER (
        ALL ( InvoiceSet[Invoice Date] );
        InvoiceSet[Invoice Date] <= MAX ( InvoiceSet[Invoice Date] )
    )
)

EL filtro debe de ir la tabla

 

FILTER (
        ALL ( InvoiceSet)

 




Lima - Peru
Greg_Deckler
Super User
Super User

The general cumulative total pattern can be found here:

http://www.daxpatterns.com/cumulative-total/

 

Basically it is:

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

I would have to see your specific data, or sample data and relationships to write a specific solution or your model.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I used the formula but it is not limiting based on the Visual Filter.

 

Here is my formula:

 

Rev_Local_Cummulative =
CALCULATE (

_Revenue[Revenue_LocalCurrency] ,

FILTER (
ALL ( DateInvoice[CalendarDate] ),
'DateInvoice'[CalendarDate] <= MAX ( 'DateInvoice'[CalendarDate] )
)
)

I have a Visualization Filter set to June 2018. June 1 starts out with the sum of all prior data and then increments by the current filtered month daily amount. 

Thanks, this worked perfectly for what I needed.

Here a sample of the data&colon;

 

sampel3.PNG

 

The report I showed an image of has bars that count the number of dates in the "Planned" and "Actual" columns and show them in bars to show the difference in the two counts. The "StartOfMonth" column is used to group the bars together by each month in the 'Shared Axis' field of the BI report. All columns are in one table. 

 

 

You should be able to use the formula that @Greg_Deckler provided and just change the SUM to a COUNT of [Planned] or [Actual].

 

 

Cummulative Actual Line =
CALCULATE (
    COUNTA ( 'Table1'[Actual] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

Cummulative Planned Line =
CALCULATE (
    COUNTA ( 'Table1'[Planned] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

 

Here is what I generated with your example data set.

Cummulative line.PNG

 

 

MWitkin
Frequent Visitor

I implemented the formulas suggested above, but the numbers illustrated don't seem to match up to the numbers on the bars.

 

Line problems.PNG

You can see the line value at the highlighted point is 8,370 while the bars is hovers by are both only showing a value of 15. It seems to just be doubling it's count of every item in the data set (there are about 550 total items in the data set by their dates span over a long period of time).

 

Did I miss a step?

 

 

Judging by the image @Twan showed of his solution, that formula should work perfectly.

Mi2n
Microsoft Employee
Microsoft Employee

what is the exact formula that you used... can you provide that?

MWitkin
Frequent Visitor

@Twan's formula was the solution. Accidentally applied the formula as a column, but it worked fine when I applied it to a measure. 

 

Thanks to everyone who helped!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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