Using one slicer (Date) with two different tables and two different data columns

Question

I have several tables in my data model.

Among others I have the table

• "Sales" (with all sold products by customer, date and marketing source) and the table
• "Media Spend" (with all marketing sources and their budget per month).

TABLE "SALES“

Product | Contract ID | Customer ID | Sales Date | Source

A | 001 | C1 | dd.mm.yyyy hh.mm.ss | Source A

B | 002 | C2 | dd.mm.yyyy hh.mm.ss | Source B

B | 003 | C1 | dd.mm.yyyy hh.mm.ss | Source B

C | 004 | C3 | dd.mm.yyyy hh.mm.ss | Source C

D | 005 | C6 | dd.mm.yyyy hh.mm.ss | Source F

TABLE „MEDIA SPEND“

Source | Spend | Campaign Month

Source A | 500 € | mm.yyyy

Source B | 600 € | mm.yyyy

Source C | 300 € | mm.yyyy

Source D | 100 € | mm.yyyy

Source E | 550 € | mm.yyyy

Source F | 1,000 € | mm.yyyy |

The tables are connected by the relation "Source".

It should be mentioned that the "Sales Date" is much more detailed (dd.mm.yyyy hh.mm.ss) than the "Campaign month") (mm.yyyy).

This allows me to filter both customers and marketing budgets by "Source". But at the same time I want to calculate / filter by Date (e.g. „Sales date“). But this is not possible.

How can I proceed to relate the two different columns in different tables?

I have already tried the following

Build the Relation based on both Date-Columns => Problem shift from Date to Source

Second connection (besides "Source") created for the columns "Sales Date" and "Campaign" month. => The data model shows the connection as dashed. Otherwise there is no effect.

THANX!

Answer 1

You can create a dates table by selecting New Table when you're in Power BI as can be seen below:

Copy and paste the below and your dates table will be created:

DimDate = CALENDAR( DATE( 2018, 1, 1 ) , DATE( 2024, 12, 31 ) )

or

DimDate = CALENDARAUTO( 3 )

Both of which will provide you with the below result:

After creating this table, you can create additional columns using the following dax for each column:

CalendarYear = YEAR( DimDate[Date] )

CalendarMonthInt = MONTH( DimDate[Date] )

CalendarDay = DAY( DimDate[Date] )

CalendarMonthName = FORMAT( DimDate[Date], "mmmm" )

CalendarShortMonth = LEFT( DimDate[CalendarMonthName], 3 )

YearMonth = CONCATENATE( YEAR( DimDate[Date] ), FORMAT( MONTH( DimDate[Date] ), "00" ) )

After adding these columns, your result will look like this: