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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
oscarII
New Member

cumulative for certain period

We are creating a measure to cumulate Sales figures, so we can see cumulated sales for the last month/year etc, or any filtered period. The table we are summing contains a line for each invoice line with the exact transaction date.

 

We are using the following formula:

 

CALCULATE(SUM('Invoice lines'[Sales]),FILTER(ALL('BI-Dates'[Date]),'BI-Dates'[Date]<=MAX('BI-Dates'[Date])),'BI-Dates'[Date])

 

However, when you filter (using a slicer) for a specific timeframe, the total at the start of that period shows the accumulated total to that point, whereas it should start at zero and then accumulate throughout the period.

 

If anyone can kindly help out, that would be great, thanks! 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@oscarII - OK played with this a bit, tested some things out and fixed a column format issue on my end. Here is what I came up with that I believe will get you what you want:

 

Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED(BI-Dates),
        'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
    )
)

The ALLSELECTED restricts the table returned to only the time range selected. I was able to put BI-Dates[Date] and Cumulative 2 in a table and watch the running total add up each day. I also used a [Month] column from BI-Dates and was able to get different (correct) cumulative totals for January versus February.

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

16 REPLIES 16
Greg_Deckler
Super User
Super User

@oscarII - OK played with this a bit, tested some things out and fixed a column format issue on my end. Here is what I came up with that I believe will get you what you want:

 

Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED(BI-Dates),
        'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
    )
)

The ALLSELECTED restricts the table returned to only the time range selected. I was able to put BI-Dates[Date] and Cumulative 2 in a table and watch the running total add up each day. I also used a [Month] column from BI-Dates and was able to get different (correct) cumulative totals for January versus February.

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

 

You should rather compare the Maximum's date to today's date like this: 

 

Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED(BI-Dates),
        'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
            && MAX ( 'BI-Dates'[Date] ) <= TODAY() ) )

 

Capture.JPG

 

Hi there, 

 

I need to have the cumulative kilometres from another table showing on this table. The cumulative kilometres would need correspond with the dates on the above table.

Is this possible?

Hi!  I am trying to use your solution and it does not seem to work for me.

 

I have tried both of the following expressions, and both return the same results.  The values returned are cumulative back to the start of the query, rather than cumulative for only the date range shown in the visual.

 

Any ideas why the addition of the allselected function does not appear to be affecting the returned totals?

 

Thank you!

 

cumulative total=
CALCULATE (
    SUM ( 'table'[column]),
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

 

-or-

 

cumulative total=
CALCULATE (
    SUM ( 'table'[column]),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

@Greg_Deckler... Just one more point on this - the cumulative amount runs past the end of the available data and you end up with a graph looking like this:

 

 

Cumulative chart.JPG

 

 

 

 

 

 

 

 

Is there any way that the formula can be amended to only show where there is data against that particular date?

 

Thanks for the help.

Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED(BI-Dates),
        'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
            && 'BI-Dates'[Date] <= TODAY()
    )
)

Just remove all dates greater than the system date from the date dimension. && is the logical and operator.

Hi,

 

I tried your approach to eliminate the future dates, however it is not filtering.

 

Cumulative Actual Units = CALCULATE(SUM('DWH W_ORDER_HEADER_D'[Sold Count]), FILTER(ALLSELECTED('DWH W_DAY_D'), 'DWH W_DAY_D'[CALENDAR_DATE] <= MAX('DWH W_DAY_D'[CALENDAR_DATE]) && 'DWH W_DAY_D'[CALENDAR_DATE] <= TODAY()))

 

Please help

 

Running Total.png

 

Thanks

Arun

Hi Arun

 

I struggle with the same issue. The expression <= TODAY() is somehow not working.

 

Did you solved this already? If yes - how?

 

Regards Patrick

Hi Willborn,

 

I know I am replying this very late. Yes we had fixed it by adding one additional column in Date Dimension Table called "TillDate". We have this table in SQL DB, and get updated on daily basis. The Till Date column will propulate all the dates till today, any records beyond today's date will be set to blank. We also have Calender Date column which is populated for all dates in Date dimension table.

 

So, for actuals till today, we are using below formula. Please note on <= MAX(datedim[TillDate]) clause.

 

Sold Cumulative = CALCULATE(Calculate(Sum(FactsActuals[OrderNetsellingPrice]),FactsActuals[Custom_Order_Status]="SOLD",FactsActuals[UnitType]<>"RETAIL")-Calculate(Sum(FactsActuals[OrderNetsellingPrice]),FactsActuals[Custom_Order_Status]="Cancelled",FactsActuals[StatusBeforeCancellation]="Sold",FactsActuals[UnitType]<>"RETAIL"),filter(ALLSELECTED(DateDim),DateDim[Calendar_date]<=max(DateDim[Tilldate])))

 

The above will be restricted because remaining dates for the year after today is set to BLANK OR NULL, so the chart will not consider blank values.

 

We use Target Sales formula as below. In this case, it uses Filter Context, where in I would have selected year as 2017, so it uses only the dates that are in the filter context and since I am considering Calendar_date column which has dates for full year. So, you will see cumulative numbers for the full year in this case.

 

Target Sales Cumulative = CALCULATE(sum(FactTarget[TargetValue]),filter(ALLSELECTED(DateDim),DateDim[Calendar_date]<=max(DateDim[Calendar_date])),FactTarget[ACTIVE_FLG]="Y")

 

Anonymous
Not applicable

Hi All

 

I've got the issue where I want my data visuals to be dynamic to the dates but for some reason it's still cumulating data from the previous dates.

 

For example, I have a data set that spreads from 01/01/2016 to the current date. I want to be able to use a drop down menu to select which year to display a chart Jan - Dec.

When I select 2016, it works fine...because I don't have any data from 2015. But when I select 2017, it takes data from 2016 and keeps cumulating into Jan. Anyone know how I can start cumulating only for the date range I've selected?

 

2016.PNG2017.PNG

 

Note, this is my formula:

 

Cumulative Spend = CALCULATE (
    SUM ( [Amount (Excl VAT)] ),
    FILTER (
        ALLSELECTED('Vendor Ledger'[Document Date].[Date]),
        'Vendor Ledger'[Document Date].[Date] <= MAX ( 'Vendor Ledger'[Document Date].[Date] )
     && 'Vendor Ledger'[Document Date].[Date] <= TODAY()  )
)

Thank you

Anonymous
Not applicable

Any one know why it does this?

 

I've selected previous 12 calendar months.

 

Capture.PNG

Anonymous
Not applicable

Expanding on ChristianH comments, I suggest using Blank() instead of 0

 

 

Cummulative By Year = IF(max(Dates[Date])>Dates[Current Date], BLANK(), CALCULATE(SUM('Table'[Field]), DATESYTD(Dates[Date])))

 

 

Hi,

 

I ran in to the same problem and fixed it with a simple IF-statement, which sets the measure to zero if the MAX of date is higher than TODAY():

Cumulative no of leads = IF(max(Dates[Dates])>today(),0,CALCULATE(COUNTA('lead'[fullname]),Filter(ALLSELECTED(Dates),Dates[Date]<=max(Dates[Date])


Regards,
Christian

Many thanks @greggyb, we needed to make some adjustments to suit our dataset but have managed to resolve this now!

@Greg_Deckler - Thanks very much, that's working perfectly now Smiley Happy

Greg_Deckler
Super User
Super User

OK, I set this up with a simple date table, BI-Dates consisting of Date and Month and an Invoice lines table consisting of Date and Sales. I then pasted in your meaure as stated below. Without a relationship between the tables, the measure always returned the same thing, the cumulative total overall. With a relationship on Date, the measure returned nothing always. So, going to play with this some more and see if I can get the running total working but you might want to check out:

 

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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.