Area chart - stop counting the stock amounts after last stock movement

[dhannaa]

Is there a good way to overcome the problem below? My chart is showing cumulative stock and it is totally correct with the last horizontal line as there are no stock movements anymore. 

However, I would prefer line to end completely at its last value.  

I did modify my  formula to return zero after today but it didn't work the way I wanted. I want no value at all instead of zero.

AccumulatedValue = 
IF(
    TODAY()<=MAX('Calendar'[Date].[Date]);
    0;
    (CALCULATE (
    SUM ( 'Item Ledger Entry'[Quantity] );
    FILTER ( ALL ( 'Calendar' ); 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ))
))

Jenny

[v-jiascu-msft · Accepted solution]

@dhannaa,

Hi Jenny,

Just try to delete .[Date] from your formula. It should work.

AccumulatedValue = 
IF(
    TODAY()<=MAX('Calendar'[Date].[Date]);
    0;
    (CALCULATE (
    SUM ( 'Item Ledger Entry'[Quantity] );
    FILTER ( ALL ( 'Calendar' ); 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ))
))

Best Regards!

Dale

[dhannaa]

Thanks,

but unfortunately Power BI doesn't let me do it. If I delete .Date, application gives me a dropdown and I have to choose Date/Day/Month/or something similar.

Jenny

[v-jiascu-msft]

Hi Jenny,

That's kindly tips from Power BI. You can ignore it and add the right parenthesis directly. Give it a try please.

Best Regards!

Dale

[dhannaa]

Thanks - that works! 

I didn't see the possibility I could ignore the dropdown 🙂

Jenny

[dhannaa]

@v-jiascu-msft

One more question regarding this. I replaced zero with BLANK() as I confronted some problems with zero values. Blank looks better but comes with a new problem as axis ends always at the end of the data -> I would want axis to continue till the end of year although there is no data for the last months.

Jenni

[v-jiascu-msft]

Hi Jenny,

Try "Show items with no data" please.

Best Regards!

Dale

[dhannaa]

Can you share your example file? This seems as a simple solution,  but I just can't get it work.

Jenni

[v-jiascu-msft]

Hi Jenni,

Check this file please.

Best Regards!

Dale

[dhannaa]

Thanks @v-jiascu-msft!

I do get the wanted result with table but as I turn it into area chart only data with values is drawn to chart. Get the same effect with your sample file:

End result is kind of misleading, as the chart is not showing the fulle year, only values till today. As dates after today would not exist 🙂

Jenny

[v-jiascu-msft]

Hi Jenny,

Try this option please.

Best Regards!

Dale