Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
@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:
Proud to be a Super User!
Paul on Linkedin.
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
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
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:
And this is what I'm looking for:
A date on which I can filter both tables.
Is this still there. The message to my mailbox went to spam, so I was not aware you replied.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |