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
webportal
Impactful Individual
Impactful Individual

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

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
Community Champion
Community Champion

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

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).

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

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

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

Sean
Community Champion
Community Champion

Is your X-Axis Continuous?

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.