Forum Discussion
Plot zero when there is no data in line chart
And same for the MovingAverage
MovingAverage = CALCULATE ( SUM ( 'Shiptracking history'[Production] ), DATESINPERIOD ( 'Shiptracking history'[Date], LASTDATE ( 'Shiptracking history'[Date] ), -10, DAY ) ) / 10 + 0Hope this helps! :smileyhappy:
Works with the sample data!
Okay hopefully this will solve it!
First things first go back to the Query Editor
1) Select the Date Column - Add Column tab - Time dropdown/button - select Time Only
2) with Date Column still selected - Date dropdown/button - select Date Only
3) Rename the original Date Column - Time and Date (or as you wish) and Date.1 rename just Date
4) Home tab - Close and Apply
Then
5) Create a Calendar Table - Modeling tab - click New Table
Calendar Table = CALENDAR ( MIN('Table'[Date]), MAX('Table'[Date]) )6) Set up the Relationship between the 2 tables based on the 2 date columns
7) And this is your New Moving Average Measure
MovingAverage Measure Calendar =
CALCULATE (
SUM ( 'Table'[Production] ),
DATESINPERIOD (
'Calendar Table'[Date],
LASTDATE ( 'Calendar Table'[Date] ),
-10,
DAY
)
)
/ 10
+ 08) You can add a "Between" Date Slicer if you wish just make sure you use the Date from the Calendar
9) Create a Line Chart - add the Date from the Calendar to the axis and deselect the Date Hierarchy if it defaults to it
10) Add the New Moving Average to the Values
Tell me this works! :smileyhappy:
Wrap the calculation in CALCULATE to have Row Context
Production Measure New = SUMX ( FILTER ( 'Calendar Table', 'Calendar Table'[Date] <= TODAY () ), CALCULATE ( SUM ( 'Table'[Production] ) + 0 ) )
This works beautifully! And I have learned a lot from this exchange. Thank you so much. Just one final question to nail this job: How can I get rid of the points that do not have data because they are in the future? Historical points without data must be shown as zero, as they are, that is great. But future points must not be shown at all. How do I tell Power BI that those are future months and that it should not plot anything from September 2018 onwards?
Production Measure 2 =
IF (
MAX ( 'Calendar Table'[Date] ) <= TODAY (),
SUM ( 'Table'[Production] ) + 0
)Wrap the calculation in CALCULATE to have Row Context
Production Measure New = SUMX ( FILTER ( 'Calendar Table', 'Calendar Table'[Date] <= TODAY () ), CALCULATE ( SUM ( 'Table'[Production] ) + 0 ) )Thank you very much! It does not work for me, see picture below. Does it work for you? It might be that I am not using your code properly. Screenshot is below.
I have tried to experiment with the addition of #N/A or na() or FALSE() or "" at the end of the IF statement to try and make it work but I was unsuccesful
Production Measure 2 = IF ( MAX ( 'Calendar Table'[Date] ) <= TODAY (), SUM ( 'Table'[Production] ) + 0, #N/A or NA() or FALSE() or "" I have tried all of these things without success )I am already very grateful for all the help you have provided. If you solve this one too, I will be delighted.
Thanks a lot
Anonymous
Sorry for the delay. Give this a try...
Production Measure New = SUMX ( FILTER ( 'Calendar Table', 'Calendar Table'[Date] <= TODAY () ), SUM ( 'Table'[Production] ) + 0 )HTH! :smileyhappy:
In fact this solution does not really give a correct result. It does get rid of the segments of the line that are in the future but it results in much larger numbers, which I suspect is because it is performing a sum twice. Check the orders of magnitude below, they have changed completely. I tried using your filter wit calculate instead of SUMX and the results are correct but the future segments came back. Do you have any other ideas for achieving my goal? Thanks
This is your last idea, which removes the future segments but results in very large (wrong) numbersThis is what I had, which results in correct numbers but plots the future segments (which I don't want)This was my attempt to use your filter without performing a sum twice, it resulted in correct results but it didn't get rid of the future segments
Thank you for your help and congratulations for finding the solution!!! :)
Sean sorry, I read this topic because I am in trouble with it.
I have a dataset imported from SQL Server in Direct Query mode and I am not able to see lines passing from 0 when I have no data:
This is my chart, I should have just 3 points at 1 in y-axis, while they all look there. The variable used here is a measure Z I wrote as:
Z = DISTINCTCOUNT(Tab[NumBrogliaccio]) + 0
On the x-axis I have Year and Month which I got from a date column.
Please help me, I read your hints but they didn't help me.
Thank you so much
Nick