cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
webportal Post Prodigy
Post Prodigy

Display empty values in area chart

I've given up trying to define a measure that returns the bank account balance and cash flow, even for days when there are no movements. After lots of different combinations, I realized it's not worth the effort.

Therefore, I'm living with BLANK for those days, which in a matrix is OK. 

However, in a Area Chart, I'd like to show the balance for all the days within the dates range. But this is showing empty values for thoses days with no movements:

Capturar.PNG

This doesn't look good with empty spots...

 

Also, why are the three series not accumulating? That's the whole idea of this chart, right?

 

Thanks for helping!

1 ACCEPTED SOLUTION

Accepted Solutions
webportal Post Prodigy
Post Prodigy

Re: Display empty values in area chart

Hi,

 

I've figured out I must have a value for each day in my dates range for the ending balance.

Otherwise, charts and visuals in general will look terrible.

 

This is the expression I've managed to find:

Saldo em EUR = 
IF (
    AND (
        'Consolidado'[DataCorrente] <= 'Consolidado'[DataMáxima];
        'Consolidado'[DataCorrente] >= 'Consolidado'[DataMínima]
    );
    CALCULATE (
        0 + SUM ( 'Consolidado'[Fluxo em EUR (intermédio)] );
        FILTER (
            ALL ( 'Calendário'[Data] );
            'Calendário'[Data] <= MAX ( 'Calendário'[Data] )
        )
    )
)

Initially, a simple measure but required to define intermediate calculations.

 

Now, the charts are looking better:

Capturar2.PNGCapturar3.PNG

View solution in original post

6 REPLIES 6
dedelman_clng Memorable Member
Memorable Member

Re: Display empty values in area chart

2nd question first - use a stacked area chart (you just have a standard area chart)

 

As to the 1st question, can you replace BLANK with 0 in the data?  There have been other dicussions that the area/stacked are chart leaves whitespots when there is missing data - it's a "bug" that seems to be low priority to be addressed.

 

You could also use a stacked column chart, which will show basically the same thing as the stacked area chart, but with "gaps" in between each X value.

 

area.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

David

webportal Post Prodigy
Post Prodigy

Re: Display empty values in area chart

Thanks a lot for your support.


The thing is, if you check your bank account on a day without transactions, you should still be able to see the balance (not a blank).

Sean Community Champion
Community Champion

Re: Display empty values in area chart

Is your X-Axis Continuous?

dedelman_clng Memorable Member
Memorable Member

Re: Display empty values in area chart

So it sounds like your value needs to be a running total?  Here is a DAX pattern for running total (assume Table1 and DateTable have a Many-to-1 relationship:

 

RunningMeasure = CALCULATE (
    SUM ( 'Table1'[Value Column]),
    FILTER (
        ALL ( 'DateTable'[Date] ),
        'Table1'[Date] <= MAX ( 'DateTable'[Date] )
    )
)

 

Hope this helps

David

webportal Post Prodigy
Post Prodigy

Re: Display empty values in area chart

Hi,

 

I've figured out I must have a value for each day in my dates range for the ending balance.

Otherwise, charts and visuals in general will look terrible.

 

This is the expression I've managed to find:

Saldo em EUR = 
IF (
    AND (
        'Consolidado'[DataCorrente] <= 'Consolidado'[DataMáxima];
        'Consolidado'[DataCorrente] >= 'Consolidado'[DataMínima]
    );
    CALCULATE (
        0 + SUM ( 'Consolidado'[Fluxo em EUR (intermédio)] );
        FILTER (
            ALL ( 'Calendário'[Data] );
            'Calendário'[Data] <= MAX ( 'Calendário'[Data] )
        )
    )
)

Initially, a simple measure but required to define intermediate calculations.

 

Now, the charts are looking better:

Capturar2.PNGCapturar3.PNG

View solution in original post

capta12
Regular Visitor

Re: Display empty values in area chart

Thank you for a good solution! However I can't get it to work on my dataset. Would you mind to share the .pbix? It would help to get an overview of the measure. 

 

Kind regards,

Anders

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors