Stacked Area Chart with Cumulative Cost - Straight line when no values

[CookieMonster]

Hi,

I have the following Stacked Area Chart

What I am trying to work out, is how I can make it do a straight line across, when no further data exists for that legend item, or if data is missing for that legend item on a particular date.

This is the measure I currently have to work out the cumulative cost:

CumulativeCost = 
CALCULATE (
    SUM ( 'Usage details'[costInBillingCurrency] ),
    FILTER (
        ALL ( 'Usage details'[date] ),
        'Usage details'[date] <= MAX ( 'Usage details'[date] )
    )
)

I hope my question makes sense and someone can help.

[parry2k]

@CookieMonster why do you expect it to start at 0 if there is data on August 01st?

[parry2k]

@CookieMonster change this too

CumulativeCost = 
CALCULATE (
    SUM ( 'Usage details'[costInBillingCurrency] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

if you read my post, one step is to mark this table as a calendar table, and seems like you missed that.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[CookieMonster]

Yes I did sorry,

I have changed it to a Date Table now, but now the lines on the graph do not start at £0.

Is this being caused by the DAX formular do you think?

[parry2k]

@CookieMonster you should add a calendar dimension in your model, you can following my blog post here to add one. Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

Once that table is added, change your measure to use date from this new calendar table and also on x-axis use date from this new table and that will do it.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[CookieMonster]

I have just tried this and it has left me with this:

So it no longer looks to be cumculative, any ideas where I went wrong?

My measure is now: 

CumulativeCost = 
CALCULATE (
    SUM ( 'Usage details'[costInBillingCurrency] ),
    FILTER (
        ALL ( 'Date'[Date].[Date] ),
        'Date'[Date].[Date] <= MAX ( 'Date'[Date] )
    )
)