cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ric Regular Visitor
Regular Visitor

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],LASTDATE('Shiptracking history'[Date]),-10,DAY))/10

Production+0 = 'Shiptracking history'[Production] + 0

Hi guys,

 

I am editing my post and providing you with more detail to make it easier for you to understand and reproduce what I am trying to do. It is not difficult but I cannot make it work. I have simplified my post as much as possible, hopefully it makes sense. The data is below. I have also pasted some screen shots that will be helpful. I am trying to generate two line charts. First, plot the total production by month. Second, plot moving average of production by day, Charts should plot every month and every day evenb if production is zero. FYI: There is no data on days when there was no production, and charts should plot zero on those days. However, Power BI seems to cut the line on the monthly chart and interpolate linearly on the daily moving average chart. Does anyone know how to sort this out? I want a continuous line on the monthly chart that was to zero in March, and a continuous moving average with value equal to zero whenever there is no data.

 

I have tried adding +0 to the measures, I have tried selecting "show items with no data" on the axis (FYI: The moving average needs to have continuous axis). I have also replaced any null with zeros in the query editor. No success so far. Hopefully one of you will solve it!

 

Thanks a lot

 

Ric

 

Capture.PNGMonthly chart needs to go to zero in MarchCapture2.PNGDaily moving average needs to go to zero in March

 

 

ProductionDate
76321.5001 Sep 2018 04:23:49
66500.0928 Aug 2018 02:18:59
81134.5025 Aug 2018 14:07:46
76908.4122 Aug 2018 15:01:31
69360.7219 Aug 2018 05:43:25
72032.4015 Aug 2018 15:52:47
77132.5413 Aug 2018 06:34:13
80747.5910 Aug 2018 04:56:34
67683.4508 Aug 2018 01:15:07
63708.3903 Aug 2018 04:03:53
78192.1001 Aug 2018 03:03:38
81134.5028 Jul 2018 13:22:14
73741.5024 Jul 2018 13:54:27
77804.0421 Jul 2018 10:09:47
66500.0918 Jul 2018 05:43:20
78608.7315 Jul 2018 09:58:07
66411.1312 Jul 2018 06:10:15
79039.0010 Jul 2018 11:23:34
73400.7206 Jul 2018 14:06:05
 06 Jul 2018 14:06:05
78192.1004 Jul 2018 19:48:11
78856.5801 Jul 2018 22:09:48
70240.1826 Jun 2018 20:15:01
78123.0725 Jun 2018 18:02:48
77591.0021 Jun 2018 21:28:23
80161.5018 Jun 2018 05:52:49
67358.5015 Jun 2018 21:09:56
78429.0611 Jun 2018 21:51:47
78345.4308 Jun 2018 05:42:45
66031.0003 Jun 2018 22:39:17
76804.0001 Jun 2018 05:17:36
80148.0029 May 2018 04:22:55
77931.6326 May 2018 01:43:00
76704.0023 May 2018 01:56:34
65953.0019 May 2018 17:09:19
78429.0616 May 2018 21:29:50
76570.9512 May 2018 22:00:39
73760.2511 May 2018 13:23:04
76856.0007 May 2018 05:22:09
78160.0004 May 2018 06:36:44
79430.0030 Apr 2018 05:41:29
78608.7326 Apr 2018 20:54:58
76128.3416 Apr 2018 02:04:09
80134.0023 Feb 2018 20:53:52
78555.0022 Feb 2018 00:06:46
69700.0018 Feb 2018 03:55:36
67025.0015 Feb 2018 20:56:57
78877.0013 Feb 2018 20:40:43
66464.0009 Feb 2018 20:11:00
65614.0006 Feb 2018 06:33:42
78052.0003 Feb 2018 06:55:48
65765.0031 Jan 2018 03:31:27
69834.0026 Jan 2018 21:18:04
78468.0023 Jan 2018 21:26:34
65949.0021 Jan 2018 22:34:08
78966.0017 Jan 2018 20:55:11
80835.0014 Jan 2018 06:56:02
80180.0012 Jan 2018 05:26:42
71047.0008 Jan 2018 00:29:23
69875.0004 Jan 2018 20:27:47
78139.0002 Jan 2018 07:42:11
66042.0028 Dec 2017 19:51:28
78464.0026 Dec 2017 21:14:36
66211.0022 Dec 2017 04:00:56
77558.0018 Dec 2017 22:12:55
69641.0016 Dec 2017 07:59:18
79998.0014 Dec 2017 06:56:43
80621.0009 Dec 2017 19:57:28
66337.0007 Dec 2017 19:40:10
77075.0003 Dec 2017 11:26:37
78289.0030 Nov 2017 13:28:18
 30 Nov 2017 13:28:18
78163.0026 Nov 2017 20:00:34
69641.0023 Nov 2017 21:31:01
74978.7521 Nov 2017 04:02:41
67617.0017 Nov 2017 21:28:42
67617.0014 Nov 2017 20:49:28
81756.7411 Nov 2017 05:05:10
70107.2509 Nov 2017 07:14:19
76497.0004 Nov 2017 05:02:47
70331.0031 Oct 2017 21:16:31
 31 Oct 2017 21:16:31
81568.2628 Oct 2017 12:07:23
69664.0024 Oct 2017 10:46:33
65587.0021 Oct 2017 02:31:34
80081.0018 Oct 2017 12:56:01
74307.0014 Oct 2017 14:05:52
 14 Oct 2017 14:05:52
76933.7011 Oct 2017 14:24:55
77961.5708 Oct 2017 04:19:37
74424.0005 Oct 2017 06:02:12
 05 Oct 2017 06:02:12
80421.4329 Sep 2017 23:23:44
80192.3026 Sep 2017 06:15:14
68695.0022 Sep 2017 22:06:52
76879.0020 Sep 2017 06:37:37
 20 Sep 2017 06:37:37
78899.0017 Sep 2017 03:19:34
67380.0013 Sep 2017 20:28:34
78148.0010 Sep 2017 22:06:13
72605.0008 Sep 2017 00:15:47
76981.0005 Sep 2017 23:21:27
80606.0002 Sep 2017 14:09:42
81193.0029 Aug 2017 14:22:36
73845.0026 Aug 2017 12:26:58
67380.0022 Aug 2017 20:56:55
69573.0019 Aug 2017 23:42:57
78853.0017 Aug 2017 05:30:49
77767.0013 Aug 2017 22:53:23
80143.0010 Aug 2017 03:52:20
75382.0006 Aug 2017 23:56:56
 06 Aug 2017 23:56:56
81146.0004 Aug 2017 06:21:04
66255.0031 Jul 2017 19:57:09
69951.0027 Jul 2017 22:41:25
76953.0025 Jul 2017 06:31:34
78335.0022 Jul 2017 21:45:02
78544.0017 Jul 2017 20:57:16
81397.8015 Jul 2017 04:38:31
68654.0010 Jul 2017 19:32:01
77277.0009 Jul 2017 05:41:07
77464.0006 Jul 2017 00:57:23
 06 Jul 2017 00:57:23
78309.0001 Jul 2017 21:40:42
 01 Jul 2017 21:40:42
67369.0029 Jun 2017 17:00:26
 29 Jun 2017 17:00:26
78090.2025 Jun 2017 21:45:45
81041.0023 Jun 2017 06:13:11
76037.0017 Jun 2017 22:48:21
68381.0015 Jun 2017 05:24:54
81468.3710 Jun 2017 04:33:16
78570.0008 Jun 2017 21:10:23
73638.0006 Jun 2017 06:15:42
 06 Jun 2017 06:15:42
56579.0003 Jun 2017 20:34:55
81656.6331 May 2017 06:26:23
78570.0026 May 2017 21:47:52
67191.0023 May 2017 10:56:33
72242.0020 May 2017 08:01:37
77957.0017 May 2017 04:58:21
76850.0013 May 2017 01:00:39
77662.0009 May 2017 00:28:32
65934.0004 May 2017 00:28:30
81258.0030 Apr 2017 02:07:10
70465.0027 Apr 2017 23:27:38
74813.0023 Apr 2017 18:43:41
77957.0020 Apr 2017 23:18:35
76250.0017 Apr 2017 01:15:53
66134.0012 Apr 2017 02:30:39
79503.0010 Apr 2017 23:05:44
69441.0008 Apr 2017 11:39:28
79480.0004 Apr 2017 01:22:23

 

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: 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
    + 0

Hope this helps! Smiley Happy

Works with the sample data!

Super User
Super User

Re: Plot zero when there is no data in line chart

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! Smiley Happy

Super User
Super User

Re: Plot zero when there is no data in line chart

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 )
)
27 REPLIES 27
Super User
Super User

Re: Plot zero when there is no data in line chart

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Plot zero when there is no data in line chart

@ric

Try these 2 things:

1) Right click on the date hierarchy in you axis and select - Show Items with No Data

2) Then add zero + 0 to the Measures that calculate the daily production

Let me know of this works! Smiley Happy

Community Support Team
Community Support Team

Re: Plot zero when there is no data in line chart

Hi @ric

 

You may try to replace the null value with "0" in query editor.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ric Regular Visitor
Regular Visitor

Re: Plot zero when there is no data in line chart

@Greg_Deckler thanks, I have added sample data and charts that illustrate the issue, hopefully that will help!

ric Regular Visitor
Regular Visitor

Re: Plot zero when there is no data in line chart

@Sean thanks, it didn't work. I have provided more details in my post, in case that is helpful

ric Regular Visitor
Regular Visitor

Re: Plot zero when there is no data in line chart

@v-cherch-msft thanks, it didn't work. I have provided more details in my post, in case that is helpful

Super User
Super User

Re: Plot zero when there is no data in line chart

This response is from my phone. So I haven’t had a chance to try the sample data.

However you need to add zero to the aggregated amounts

Production+0 = SUM ( 'Shiptracking history'[Production] )  + 0

and same with the moving average Smiley Happy

ric Regular Visitor
Regular Visitor

Re: Plot zero when there is no data in line chart

Thanks! I might be not understanding what you mean... Check out what I get if I do what I think you mean. I get a very large number, I don't know where that number comes from!

 

Capture3.PNGThat sum results in a large number which I don't recognise

 

Capture4.PNGThe correct order of magnitude is ~0.7M, not 300M

Super User
Super User

Re: Plot zero when there is no data in line chart

@ric

That should me a MEASURE not a COLUMN

Production Measure = SUM ( 'Table'[Production] ) + 0

Then place the Measure in the Values area of your Line Chart.

That should do it! Smiley Happy