Table Relationships for Actual vs Budget analysis

[Anonymous]

I am preparing a dashboard for Budget vs. Actual sales analysis. I have different lookup tables, one data table and three budget tables. I am facing difficulty in creating right type of relationships that will join all these tables to help me in creating KPI and other visuals. I cant combine them in one data table rather I have to keep all these table separately.  Please help https://www.dropbox.com/sh/3bbrcpezh5d8h9u/AACDR0gNFc6pAtcvcpOctYaTa?dl=0

[mussaenda]

Hi @Anonymous ,

Thank you for providing a dummy data.

Here you can see how they are related.

I added one more table (product lookup table)

Is this okay with you?

Others will have more efficient answer. 

[Anonymous]

Thanks for your support and taking the time to solve this query. I am trying to replicate it but please tell me what new columns you have created in Target, Calendar and Revenue tables.

[mussaenda]

Hi @Anonymous ,

link of pbix is here

You can see the new columns created

[Anonymous]

I applied the solution but now facing another error. I have some time intelligence functions in my dashboard which I am using to create bar charts showing current and same period last year results. here is the error message.

"Function SAMEPERIODLASTYEAR expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship."

Please help me to resolve. I dont want to change the cross filter diection.

https://www.dropbox.com/s/2iotg0ckh1nb2c9/Error.PNG?dl=0

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution. if not, please share more details about your question, we'd like to provide further support. thanks!

[mussaenda]

Refer to this thread:

https://community.powerbi.com/t5/Desktop/Function-SAMEPERIODLASTYEAR-expects-a-contiguous-selection-...