Insert Date Field in the Market Data table

[JorgeAbiad]

Hello PBI experts,

I have a requirement to create probably bar/line chart show the budget and forecast values for each year starting from 2020 up to 2039. But the budget and forecast for each year are included in the same table.

In the required bar/line chart, the X-axis should be representd by Year. But the problem is there is no date dimension in the table that relates directly to forecast or budget. My question is how do I insert a Year field? Please help as I've been struggling on this for 2 weeks. Please  see below for sample data.

Market Type

Name

Customer Group

Budget 2024

Budget 2022

Budget 2020

Budget 2021

Budget 2023

Country

Platform

Current FC 2024

Current FC 2023

Current FC 2022

Current FC 2021

Current FC 2020

Brand

Region

Commercial Vehicle

MD market data

Cust Group Test

Platform Light Vehicle

Another Market Data

Cust Group Test

Platform Commercial Vehicle

market data 3

Cust Group Test

Platform Light Vehicle

MQB37

Volkswagen

Platform Light Vehicle

MQB38

Volkswagen

Platform Light Vehicle

MLB(w)

Volkswagen

Platform Light Vehicle

MLB49

Volkswagen

Light Vehicle

A3 (-3) SB/LIM (CN)

Volkswagen

100000

100000

100000

100000

100000

China

MQB37

100000

100000

100000

100000

100000

AUDI

Asia

Light Vehicle

A3 (-4)SB/ Lim (CN)

Volkswagen

100000

100000

100000

100000

100000

China

MQB38

100000

100000

100000

100000

100000

AUDI

Asia

Light Vehicle

A4L (B10) (CN)

Volkswagen

100000

100000

100000

100000

100000

China

MLB(w)

100000

100000

100000

100000

100000

AUDI

Asia

Light Vehicle

A4L (B9) (CN)

Volkswagen

100000

100000

100000

100000

100000

China

MLB49

100000

100000

100000

100000

100000

AUDI

Asia

Thanks

JorgeAbiad

[v-lid-msft · Accepted solution]

Hi @JorgeAbiad ,

We can use pivot to meet your requirement

1. Select All the budget and current fc columns, then pivot them

2. Replace the  “Current FC" with "Current_FC" to make split more easier

3. Split Attribute column with space

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.

Best regards,

[v-lid-msft · Accepted solution]

Hi @JorgeAbiad ,

We can use pivot to meet your requirement

1. Select All the budget and current fc columns, then pivot them

2. Replace the  “Current FC" with "Current_FC" to make split more easier

3. Split Attribute column with space

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.

Best regards,

[JorgeAbiad]

Hello @v-lid-msft ,

Thank you for your response. I will try this approach if this will work according to the requirement.

I'm quite new to Power BI so I am not yet fully aware of its awesome features.

I would like to clarify the following questions:

1. How do I choose all the Budget and Forecast columns? Sorry but I could not find the way to select multiple columns all at the same time.

2. After pivoting the columns selected above, will they remain in the original table?

3. What will happen to the new records added to the original table? Will they be transformed or pivoted automatically?

Thank you very much again:)

Regards

JorgeAbiad

[v-lid-msft]

Hi @JorgeAbiad ,

Sorry for our late reply, We can use ctrl to multi choose the column,  After pivoting the columns selected above, they will not remain in the original table, if you want to keep the origin table, we suggest you to duplicate one, If the new record is row, it will be pivote after each refresh, but the new column might need to change the query.

Best regards,

[JorgeAbiad]

Hello @v-lid-msft ,

Please see attached file. It contains the data after unpivoting the columns.

Is it possible to create a line chart out of the three columns?

Thanks:)

Regards,

JorgeAbiad

[JorgeAbiad]

Hello @v-lid-msft 

Thank you very much!

I was able to do it. However, that leads me to another requirement now. How am I going to create a line  chart to show the budget and current forecast for each year(2020 - 2036)? Or this even possible? Do I need to calculte the budget and forecast for each year?

Regards,

JorgeAbiad

[v-lid-msft · Accepted solution]

Hi @JorgeAbiad ,

We can just put the fields into line chart as following after pivot to meet your requirement:

Best regards,

[JorgeAbiad]

Hello  @v-lid-msft,

Thank you for all the support you have provided so far.

I have a new requirement which I think seems to be a bit complex. In the matrix below, there are years when there is data for the budget. The deviation for each year between budget and forecast is required. 

I'm not sure if the simple Current Forecast - Budget will do the correct calculation considering there are null values in the Budget row. My question is how to get the correct deviation or difference>

[JorgeAbiad]

Hello PBI experts,

If you think of any possible solution to this, please share it.

Thanks

Regards

JorgeAbiad