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] + 0Hi 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
| Production | Date |
| 76321.50 | 01 Sep 2018 04:23:49 |
| 66500.09 | 28 Aug 2018 02:18:59 |
| 81134.50 | 25 Aug 2018 14:07:46 |
| 76908.41 | 22 Aug 2018 15:01:31 |
| 69360.72 | 19 Aug 2018 05:43:25 |
| 72032.40 | 15 Aug 2018 15:52:47 |
| 77132.54 | 13 Aug 2018 06:34:13 |
| 80747.59 | 10 Aug 2018 04:56:34 |
| 67683.45 | 08 Aug 2018 01:15:07 |
| 63708.39 | 03 Aug 2018 04:03:53 |
| 78192.10 | 01 Aug 2018 03:03:38 |
| 81134.50 | 28 Jul 2018 13:22:14 |
| 73741.50 | 24 Jul 2018 13:54:27 |
| 77804.04 | 21 Jul 2018 10:09:47 |
| 66500.09 | 18 Jul 2018 05:43:20 |
| 78608.73 | 15 Jul 2018 09:58:07 |
| 66411.13 | 12 Jul 2018 06:10:15 |
| 79039.00 | 10 Jul 2018 11:23:34 |
| 73400.72 | 06 Jul 2018 14:06:05 |
| 06 Jul 2018 14:06:05 | |
| 78192.10 | 04 Jul 2018 19:48:11 |
| 78856.58 | 01 Jul 2018 22:09:48 |
| 70240.18 | 26 Jun 2018 20:15:01 |
| 78123.07 | 25 Jun 2018 18:02:48 |
| 77591.00 | 21 Jun 2018 21:28:23 |
| 80161.50 | 18 Jun 2018 05:52:49 |
| 67358.50 | 15 Jun 2018 21:09:56 |
| 78429.06 | 11 Jun 2018 21:51:47 |
| 78345.43 | 08 Jun 2018 05:42:45 |
| 66031.00 | 03 Jun 2018 22:39:17 |
| 76804.00 | 01 Jun 2018 05:17:36 |
| 80148.00 | 29 May 2018 04:22:55 |
| 77931.63 | 26 May 2018 01:43:00 |
| 76704.00 | 23 May 2018 01:56:34 |
| 65953.00 | 19 May 2018 17:09:19 |
| 78429.06 | 16 May 2018 21:29:50 |
| 76570.95 | 12 May 2018 22:00:39 |
| 73760.25 | 11 May 2018 13:23:04 |
| 76856.00 | 07 May 2018 05:22:09 |
| 78160.00 | 04 May 2018 06:36:44 |
| 79430.00 | 30 Apr 2018 05:41:29 |
| 78608.73 | 26 Apr 2018 20:54:58 |
| 76128.34 | 16 Apr 2018 02:04:09 |
| 80134.00 | 23 Feb 2018 20:53:52 |
| 78555.00 | 22 Feb 2018 00:06:46 |
| 69700.00 | 18 Feb 2018 03:55:36 |
| 67025.00 | 15 Feb 2018 20:56:57 |
| 78877.00 | 13 Feb 2018 20:40:43 |
| 66464.00 | 09 Feb 2018 20:11:00 |
| 65614.00 | 06 Feb 2018 06:33:42 |
| 78052.00 | 03 Feb 2018 06:55:48 |
| 65765.00 | 31 Jan 2018 03:31:27 |
| 69834.00 | 26 Jan 2018 21:18:04 |
| 78468.00 | 23 Jan 2018 21:26:34 |
| 65949.00 | 21 Jan 2018 22:34:08 |
| 78966.00 | 17 Jan 2018 20:55:11 |
| 80835.00 | 14 Jan 2018 06:56:02 |
| 80180.00 | 12 Jan 2018 05:26:42 |
| 71047.00 | 08 Jan 2018 00:29:23 |
| 69875.00 | 04 Jan 2018 20:27:47 |
| 78139.00 | 02 Jan 2018 07:42:11 |
| 66042.00 | 28 Dec 2017 19:51:28 |
| 78464.00 | 26 Dec 2017 21:14:36 |
| 66211.00 | 22 Dec 2017 04:00:56 |
| 77558.00 | 18 Dec 2017 22:12:55 |
| 69641.00 | 16 Dec 2017 07:59:18 |
| 79998.00 | 14 Dec 2017 06:56:43 |
| 80621.00 | 09 Dec 2017 19:57:28 |
| 66337.00 | 07 Dec 2017 19:40:10 |
| 77075.00 | 03 Dec 2017 11:26:37 |
| 78289.00 | 30 Nov 2017 13:28:18 |
| 30 Nov 2017 13:28:18 | |
| 78163.00 | 26 Nov 2017 20:00:34 |
| 69641.00 | 23 Nov 2017 21:31:01 |
| 74978.75 | 21 Nov 2017 04:02:41 |
| 67617.00 | 17 Nov 2017 21:28:42 |
| 67617.00 | 14 Nov 2017 20:49:28 |
| 81756.74 | 11 Nov 2017 05:05:10 |
| 70107.25 | 09 Nov 2017 07:14:19 |
| 76497.00 | 04 Nov 2017 05:02:47 |
| 70331.00 | 31 Oct 2017 21:16:31 |
| 31 Oct 2017 21:16:31 | |
| 81568.26 | 28 Oct 2017 12:07:23 |
| 69664.00 | 24 Oct 2017 10:46:33 |
| 65587.00 | 21 Oct 2017 02:31:34 |
| 80081.00 | 18 Oct 2017 12:56:01 |
| 74307.00 | 14 Oct 2017 14:05:52 |
| 14 Oct 2017 14:05:52 | |
| 76933.70 | 11 Oct 2017 14:24:55 |
| 77961.57 | 08 Oct 2017 04:19:37 |
| 74424.00 | 05 Oct 2017 06:02:12 |
| 05 Oct 2017 06:02:12 | |
| 80421.43 | 29 Sep 2017 23:23:44 |
| 80192.30 | 26 Sep 2017 06:15:14 |
| 68695.00 | 22 Sep 2017 22:06:52 |
| 76879.00 | 20 Sep 2017 06:37:37 |
| 20 Sep 2017 06:37:37 | |
| 78899.00 | 17 Sep 2017 03:19:34 |
| 67380.00 | 13 Sep 2017 20:28:34 |
| 78148.00 | 10 Sep 2017 22:06:13 |
| 72605.00 | 08 Sep 2017 00:15:47 |
| 76981.00 | 05 Sep 2017 23:21:27 |
| 80606.00 | 02 Sep 2017 14:09:42 |
| 81193.00 | 29 Aug 2017 14:22:36 |
| 73845.00 | 26 Aug 2017 12:26:58 |
| 67380.00 | 22 Aug 2017 20:56:55 |
| 69573.00 | 19 Aug 2017 23:42:57 |
| 78853.00 | 17 Aug 2017 05:30:49 |
| 77767.00 | 13 Aug 2017 22:53:23 |
| 80143.00 | 10 Aug 2017 03:52:20 |
| 75382.00 | 06 Aug 2017 23:56:56 |
| 06 Aug 2017 23:56:56 | |
| 81146.00 | 04 Aug 2017 06:21:04 |
| 66255.00 | 31 Jul 2017 19:57:09 |
| 69951.00 | 27 Jul 2017 22:41:25 |
| 76953.00 | 25 Jul 2017 06:31:34 |
| 78335.00 | 22 Jul 2017 21:45:02 |
| 78544.00 | 17 Jul 2017 20:57:16 |
| 81397.80 | 15 Jul 2017 04:38:31 |
| 68654.00 | 10 Jul 2017 19:32:01 |
| 77277.00 | 09 Jul 2017 05:41:07 |
| 77464.00 | 06 Jul 2017 00:57:23 |
| 06 Jul 2017 00:57:23 | |
| 78309.00 | 01 Jul 2017 21:40:42 |
| 01 Jul 2017 21:40:42 | |
| 67369.00 | 29 Jun 2017 17:00:26 |
| 29 Jun 2017 17:00:26 | |
| 78090.20 | 25 Jun 2017 21:45:45 |
| 81041.00 | 23 Jun 2017 06:13:11 |
| 76037.00 | 17 Jun 2017 22:48:21 |
| 68381.00 | 15 Jun 2017 05:24:54 |
| 81468.37 | 10 Jun 2017 04:33:16 |
| 78570.00 | 08 Jun 2017 21:10:23 |
| 73638.00 | 06 Jun 2017 06:15:42 |
| 06 Jun 2017 06:15:42 | |
| 56579.00 | 03 Jun 2017 20:34:55 |
| 81656.63 | 31 May 2017 06:26:23 |
| 78570.00 | 26 May 2017 21:47:52 |
| 67191.00 | 23 May 2017 10:56:33 |
| 72242.00 | 20 May 2017 08:01:37 |
| 77957.00 | 17 May 2017 04:58:21 |
| 76850.00 | 13 May 2017 01:00:39 |
| 77662.00 | 09 May 2017 00:28:32 |
| 65934.00 | 04 May 2017 00:28:30 |
| 81258.00 | 30 Apr 2017 02:07:10 |
| 70465.00 | 27 Apr 2017 23:27:38 |
| 74813.00 | 23 Apr 2017 18:43:41 |
| 77957.00 | 20 Apr 2017 23:18:35 |
| 76250.00 | 17 Apr 2017 01:15:53 |
| 66134.00 | 12 Apr 2017 02:30:39 |
| 79503.00 | 10 Apr 2017 23:05:44 |
| 69441.00 | 08 Apr 2017 11:39:28 |
| 79480.00 | 04 Apr 2017 01:22:23 |
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! 
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
+ 0
😎 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!
@Anonymous
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!
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
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!
@Anonymous
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!
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!
Works with the sample data!
@Sean Thank you. The Moving Average does not work for me... I created a measure copy-pasting your code. Power BI is still interpolating when data has gaps. In March there was no production so the 10-day moving average should be zero. Does it work for you? What am I doing wrong here?
Yes works with the sample data you provided!
EDIT: I switched to Categorical Axis only for the picture above - to show March 2018 specifically!
@Sean thanks so much for your help and patience. I am doing something wrong. Maybe the problem is with my axis. Check the picture below, it does not work for me. The title says "by Date" whereas yours says "by Year and Month". I wonder if that may be the reason why it does not work for me? It is odd that it works for you and not for me, surely I am making a mistake somewhere.
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
😎 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!
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?
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!
Helpful resources
Power BI September 2023 Update
Take a look at the September 2023 Power BI update to learn more.
