Joining sales & budget tables via a master date table

[Anonymous]

I have the following schema, sales and budget linked via a date master table.

When I try to load the sales budget figures on my matrix in the context of channel, however it just gives the total budget for the month and not the per channel budget:

(the channels are under the blue marker)

I can get the channel sales if I join between sales and budget joined on  channel but then I can't use the master date "month" to use the intellisense functions for functions like SAMEPERIODLASTYEAR etc.

Any help would be greatly appreciated

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Due to I don't know your data model, I build three tables to have a test.

Calendar Table:

Calendar = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2020,12,31)) ,"Month",MONTH([Date]))

Add calculated column:

Month Name = CALCULATE(MAX('Month order'[Month Name]),FILTER('Month order','Month order'[Month]=EARLIER('Calendar'[Month])))

Result:

Sales Table:

Budge Table:

And I build relationships between Date columns in three tables.

I think you may use channel column in Sales table to build the matrix, and I get the same issue like yours.

You can build a new Channel table, build relationships between Channel columns in new table and Sales/Budge Table and use channel column in new table to build the matrix:

Channel Table:

Channel = VALUES(Budge[Channel])

Relationships:

Result:

However this way is not recommended, you may build a measure to solve this problem.

Measure_Value = 
IF (
    ISINSCOPE ( Sales[Channel] ),
    CALCULATE (
        SUM ( Budge[Value] ),
        FILTER ( Budge, Budge[Channel] = MAX ( Sales[Channel] ) )
    ),
    SUM ( Budge[Value] )
)

Result:

You can download the pbix file from this link: Joining sales & budget tables via a master date table

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

thank you for the solution, I tried joining the tables via another channel table and it works really well so I just did it this way.  

I also used the measure solution to solve a further issue I was having with open orders and back orders on another presentation I was building and this works great also, thanks!

[amitchandak]

@Anonymous , it has the date you should able to use all time intelligence functions. I can see Date and channel in budget, you should able to analyze data by channel and time and use time intelligence.

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Daily sales - Monthly Target -https://youtu.be/yPQ9UV37LOU