cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trader Frequent Visitor
Frequent Visitor

Extra rows so that all periods have a value

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

2 REPLIES 2
Community Support Team
Community Support Team

Re: Extra rows so that all periods have a value

@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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trader Frequent Visitor
Frequent Visitor

Re: Extra rows so that all periods have a value

@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

 

LocationPeriodActFcst
C15050
D15050
A26060
C34242
C47070
W58080
D61010
A62020
W62626
A8 40
D9 50
C10 60
C12 70
W12 80

 

 

 

My next table is called Date

PeriodMonth
11/07/2017
21/08/2017
31/09/2017
41/10/2017
51/11/2017
61/12/2017
71/01/2018
81/02/2018
91/03/2018
101/04/2018
111/05/2018
121/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)

 

LocationPeriodtest
Christchurch150
Christchurch250
Christchurch392
Christchurch4162
Christchurch5162
Christchurch6162
Christchurch7162
Christchurch8162
Christchurch9162
Christchurch10222
Christchurch12292

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 20 members 745 guests
Please welcome our newest community members: