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
lefinalzugzwang
Frequent Visitor

stuck trying to create a cumulative area chart with DAX

TL;DR underlined and bold Hi all, I am attempting to create an area chart visual to show the production plan of a given product my company makes so that my staff can see stock issues on the horizon and raise purchase orders.

Ideally I would have a cumulative area chart showing three key things cumulative Forecasts[Forecast.Volume] by month, cumulative Sales[Sale.Volume] by month, and finally cumulative planned stock build by month (more on that later).

Here is what I currently have set up:

 

Production PlanningProduction Planning

The relationships between forecasts and sales are working perfectly (using a date table and a product table). What I am missing (apart from a bunch of sales in Jan and Feb :p) is the cumulative behaiviour, and my research hasn't really brought me any closer to creating that. 

The second thing missing is cumulative planned stockbuild. Planned Stockbuild of a product consists of Stocks[Stock.InStock] filtered by Stocks[Stock.AsAtDate]=Today (AsAt Dates are related to a seperate date table due to the different nature of them - anyway I don't have issues with this part) together with Purchases[Purchase.Quantity] which are flowing in to the warehous at Purchases[Purchase.Date]. This should be a fairly simple calculation - I really need to become more fluent in DAX because I should really be able to do this (I ordered a few books from amazon which will arrive soon!). 

Thanks in advance for any advice from DAX experts out there!

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

Hi @lefinalzugzwang,

What you describe is a expected behavior, when you use the above DAX formula to create cumulate total, at any given date, the cumulative measure displays the sum of Sales.Volume made on a date that is less than or equal to the selected date. In other words, when you filter the year to 2017, the measure will corresponds to the sum of year 2016 sales, year 2015 sales and previous year sales as long as you have data in the previous year. For more details about cumulate total, please review this log: http://www.daxpatterns.com/cumulative-total/ .

Besides, what is your expected data after filtering year to 2017? If you just want to show sum of year 2017 sales, just drag Sale.Volume field to your area chart or you can change formula of your measure to the following.

Sale.CumulativeVolume =
CALCULATE (
    SUM ( Sales[Sale.Volume] ),
    FILTER (
        ALLSELECTED( 'Dates'[Date] ),
        'Dates'[Date] <= MAX ( 'Dates'[Date] )
    )
)

Thanks,
Lydia Zhang

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.

Hi @lefinalzugzwang 

 

In later versions the TotalMTD, TotalQTD, TotalYTD functions work very well. These can also be combined with USERELATIONSHIP for other date fields in the data set that also have relationships to the date table.

 

Dawid van Heerden

Twitter: @dawidvh

YouTube: https://www.youtube.com/davestechtips

**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions. AND we get points for it 😉

lefinalzugzwang
Frequent Visitor

I since managed to get some cumulative behaiviour going on. It worked successfully for forecasts, but unfortunately not for Sales. I created two measures, the Actuals one for example was defined by:

Sale.CumulativeVolume = 
CALCULATE (
    SUM ( Sales[Sale.Volume] ),
    FILTER (
        ALL ( 'Dates'[Date] ),
        'Dates'[Date] <= MAX ( 'Dates'[Date] )
    )
)

When I used this measure in my area chart instead of Sale.Volume the product filtering worked perfectly. But somehow previous years sales were picked up by the measure even though I had filtered the dates at the page/visual level by the current year. Forecast.CumulativeVolume didn't suffer from this because there is no data in the table that's not dated 2017. So i'm starting to get somewhere but can't quite figure out exactly what I need in my FILTER()


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.