Budget in a table - no relationship

[lekkerbek]

Hi,

I want to add a budget to my report, so I created the attached excelsheet. Several GL accounts with different costcentres (GtM in this case) and a value for each month.

In my model I have a date table and a table with all the actual numbers.

If I try to create a relationship it says I have not a column with unique values. That is correct, but how do I resolve this?

I can create a relationship with the seperate GtM table, but when I try to create a chart and use the monthname from the date table and values from the budget tables, it gives an error.

[vanessafvg]

@lekkerbek in power query duplicated the table with all the GL accounts and remove all other columns and keep the gl accounts,  remove duplicates from the column, then place that table between your two tables

[lekkerbek]

Hi,

Thanks it's partially working. I have some figures now, but it's a total for the whole year. 

It's seems like there is a missing link with the date table. 

I use the GtM and "Bedrag" (value) of the Budget table

I use the MonthNameShort from the Date table

[v-huizhn-msft]

Hi @lekkerbek,

Based on your description, I am not get your requirement. Which link miss? Please share mode details for further analysis.

Best Regards,
Angelia

[lekkerbek]

I have a table which contains all the journals of the year 2017 of the financial system. The table is called TwinCube.

I made a table in Excel with budget figures for 2017. So basically:

- general ledger number

- costcentre (=GtM)

- month (I used the last day of each month, so 31-1-2017, 28-2-2017, etc)

- amount

I also have a date table

If I create a line chart with the date and amounts both from the Budget table, the line chart is fine

If I create a line chart with the date and amounts both from the Twincube table, the line chart is fine

If I create a line chart with the date from the date table and the amounts from the Twincube table, the line chart is fine

However, if I create a line chart with the amounts from the Twincube and the Budget table and the dates from the date table, the actual figures are fine, but the budget shows a flat line.

I think the problem is that there isn't a correct (direct or indirect) relationship between the date table and the budget table.

[v-huizhn-msft · Accepted solution]

Hi @lekkerbek,

Please transfer the both to single filter direction, and check if it works fine.



In addition, could you please share your .pbix for further analysis?

Best Regards,
Angelia

[lekkerbek]

I created a relationship between the budget table and date table and changed it to single direction as per your suggestion and now it works like a charm.

I must have done something wrong previously cause I wasn't able to create the relationship between those two tables before.

Anyway solved.

Thanks for your help and effort.