The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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
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
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
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
Thanks - that works!
I didn't see the possibility I could ignore the dropdown 🙂
Jenny
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
Hi Jenny,
Try "Show items with no data" please.
Best Regards!
Dale
Can you share your example file? This seems as a simple solution, but I just can't get it work.
Jenni
Hi Jenni,
Check this file please.
Best Regards!
Dale
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
Hi Jenny,
Try this option please.
Best Regards!
Dale
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |