Using a date table across all tables of a report

[Anonymous]

Hello,

I'm struggling to understand how to make date tables work in Power BI.

I have two tables, one containing the budget for a year, month by month.

My second table contains accounting data, also for a year, month by month.

I'd like to compare the budget with the accounting for any given month (by using a slicer on the date, as a "choose a month" functionality) but I can't get the date table to be used for the two tables at the same time. I can only have one relationship active with it, so only one of my two tables is affected by the date slicer.

I'd love to get some help on this.

Here is some sample data I used:

Budget table

Category	Date	Amount
A	Jul 2019	100
B	Jul 2019	120
A	Aug 2019	50
C	Aug 2019	130
A	Sep 2019	75
C	Sep2019	100

Accounting Table

Category	Date	Amount
A	Jul 2019	120
B	Jul 2019	100
A	Aug 2019	50
C	Aug 2019	125
A	Sep 2019	110
C	Sep 2019	95

My goal here is to compare the amount of each category of the budget to the same category from the accounting, while filter on the date column. However as stated above I can only have one active relationship with the date table, either budget or accounting. The other will be unaffected by the slicer.

Thanks !

[PaulDBrown]

@Anonymous 

In my experience, the best way to deal with these type of situations is to create a lookup table (calendar or period table) which you then join to each of your tables with a single-to-many relationship. For this to work best, you need a continuous period without duplicate values. So in your case, since you are covering year and month, the suggested method is to include a numeric column for YearMonth (which is a simple table[year] * 100 + table[month], (as well as the month number, month name and year columns)

This Calendar/period table is what you then use in all your meaures, slicers and visuals as the filter context for the time period.

In effect, when you filter (via slicers, filters or measure) the calendar/period table will filter the relevant rows in each of your budget and Accounting table, enabling the aggregators to be comparable. I'm probably making it sound much more complicated than it actually is!

I also created a lookup table for category for the same purpose (which I used in the table visual below to provide the right filter context).

Here is a screenshot of the relationships between tables:

and another with filtered data:

 and here is the PBI file if you're interested in exploring:

Sample PBI with lookup tables 

[amitchandak]

Create a month date table as given below. Change the format of Month

MonthTable = 
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

Join both tables on month -year. Idle is that you have a join on the date. (The above one is non-continuous date calendar, idle is continuous, means you have a month-end date in your fact tables)

You should able to create a join of the date table with both tables in active mode. Same table two joins cannot be active.

Same table two date joins refer

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak 

This solution seems to work if both tables have the exact same number of rows. If I join, some rows from the table with the least amount of rows will be doubled/tripled, and this affects the numbers of course.

However, my accounting table has many more rows. I should have stated this in my intial message.

Here is some more accurate data:

Budget table

Date	Category	Amount
07.2019	R&D	100
07.2019	Administrative	120
08.2019	R&D	150
08.2019	Administrative	75
08.2019	Wages	100
09.2019	R&D	135
09.2019	Administrative	95
09.2019	Wages	100
10.2019	Administrative	20
10.2019	Wages	250
11.2019	Administrative	200

Accounting table

Date	Category	Description	Amount
07.2019	R&D	IT Costs	50
07.2019	R&D	Licenses	60
07.2019	Administrative	Rent	80
07.2019	Administrative	Office supplies	45
08.2019	R&D	IT Costs	100
08.2019	R&D	Licenses	70
08.2019	Administrative	Rent	75
08.2019	Wages	Salaries	95
09.2019	R&D	IT Costs	120
09.2019	R&D	Licenses	40
09.2019	Administrative	Rent	90
09.2019	Wages	Salaries	105
10.2019	Administrative	Rent	20
10.2019	Administrative	Office supplies	10
10.2019	Wages	Salaries	240
11.2019	Administrative	Rent	185

I have the following relationship between the two tables in order to compare the amount in a table/matrix:

https://imgur.com/a/uWfxrbE

And this is what I'm looking for:

https://imgur.com/a/8s1L0rp

A date on which I can filter both tables.

[amitchandak]

Is this still there. The message to my mailbox went to spam, so I was not aware you replied.