Forum Discussion

webportal's avatar
webportal
Impactful Individual
9 years ago
Solved

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 wort...
Need Help
  • webportal's avatar
    webportal
    9 years ago

    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:

dedelman_clng's avatar
dedelman_clng
Community Champion
9 years ago

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's avatar
webportal
Impactful Individual
9 years ago

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:

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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