Extra rows so that all periods have a value

[trader]

Hi

I am building a forecast YTD chart that has the months along the x-axis and the YTD values on the Yaxis. If the month has been we use actuals otherwise we use the forecast monthly values.

I have encountered some problem with my SAP data.  Basically for some cost lines I have actuals but since we have spent our budget we have nothing entered in the forecast (i.e. for these lines the max row I have is the last actual period).  For other lines then I have values for every period in either the actual or forecast columns.

I have created a dummy table with every combination (my data has profit centre and cost elements)  which is quite large and then used merge to get the extra rows into the dataset (i.e. ensure I have a period 12 for all profit centre and cost element combo), however this seems like an ineffienct solution.  

Is there an easier way?

thanks

[v-chuncz-msft]

@trader,

Given a period table, you may just use DAX to add a measure.

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-measures

[trader]

@v-chuncz-msft

Hi, thanks for your reply.  I am relatively new to this tool so please forgive my question as I am struggling to understand

I create a simple view of my dataset below.

Table 1

Location	Period	Act	Fcst
C	1	50	50
D	1	50	50
A	2	60	60
C	3	42	42
C	4	70	70
W	5	80	80
D	6	10	10
A	6	20	20
W	6	26	26
A	8		40
D	9		50
C	10		60
C	12		70
W	12		80

My next table is called Date

Period	Month
1	1/07/2017
2	1/08/2017
3	1/09/2017
4	1/10/2017
5	1/11/2017
6	1/12/2017
7	1/01/2018
8	1/02/2018
9	1/03/2018
10	1/04/2018
11	1/05/2018
12	1/06/2018

I then created 2 measure based on readings

Measure 1 = Sum(Table1[Act])

Measure 2 = TOTALYTD([Measure1],'Date'[Month])

I also tried a new column with TOTALYTD(sum(Table1[Amt]),'Date'[Month],filter(Table1,Table1[Location]=earlier(Table1[Location]))) which gives me the right numbers for the periods where this is data but still misses out the periods where there is not any.

What I am looking to see is a table/chart that for Location C above would look like

For Filter on Location = C (the yellow highlighted rows are missing and therfore distorting my chart, this is especially a problme if period 12 is missing)

Location	Period	test
Christchurch	1	50
Christchurch	2	50
Christchurch	3	92
Christchurch	4	162
Christchurch	5	162
Christchurch	6	162
Christchurch	7	162
Christchurch	8	162
Christchurch	9	162
Christchurch	10	222
Christchurch	12	292