Anonymous

Not applicable

8 years ago

Solved

Plot zero when there is no data in line chart

Month = month('Shiptracking history'[Date]) year = year('Shiptracking history'[Date]) MovingAverage = calculate(sum('Shiptracking history'[Production+0]),DATESINPERIOD('Shiptracking history'[Date],...

Need Help

Sean

8 years ago

And same for the MovingAverage

MovingAverage =
CALCULATE (
    SUM ( 'Shiptracking history'[Production] ),
    DATESINPERIOD (
        'Shiptracking history'[Date],
        LASTDATE ( 'Shiptracking history'[Date] ),
        -10,
        DAY
    )
)
    / 10
    + 0

Hope this helps! :smileyhappy:

Works with the sample data!

Sean
8 years ago
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
    + 0
8) 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:

Sean

8 years ago

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

Sean
Community Champion
8 years ago
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
    + 0
8) 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:

Sean

Community Champion

8 years ago

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

Anonymous
Not applicable
8 years ago
Sean
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

Sean

Community Champion

8 years ago

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:

Sean
Community Champion
8 years ago
Do you have a Calendar Table?
Anonymous
Not applicable
8 years ago
No, I think I don't have a calendar table. I have read about calendar tables. How am I meant to use it in this case?

This is what my data looks like
Anonymous
Not applicable
8 years ago
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?

Sean

Community Champion

8 years ago

Production Measure 2 =
IF (
    MAX ( 'Calendar Table'[Date] ) <= TODAY (),
    SUM ( 'Table'[Production] ) + 0
)

Anonymous
Not applicable
8 years ago
Sean
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
Anonymous
Not applicable
8 years ago
Sean
Thank you for your help and congratulations for finding the solution!!! :)
Anonymous
Not applicable
7 years ago
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
Drew248
Frequent Visitor
7 years ago
Hello Sean Sean , is there a way to do this if i have my date as "2018-01" format for example. i can format it as a date but its still not working for me.

thank you

heres a sample of the measure i created thus far

All Other Customers Qty = CALCULATE(SUM(Data[QTY]),Data[customer account]<>"WAY010414",Data[customer account]<>"WAY010090")

Forum Discussion

Plot zero when there is no data in line chart

Related content

problem while plotting Scatter plot

Need help with plotting a line chart

Dot Plot Chart

How to use Sankey Chart and plot visual.

Line chart does not plot data between some points