cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## How to Plot Date (Month) Vs Revenue Vs Cost? Date columns must combine from 2 different Tables

Hello all,

I have a Table "Invoice" has columns "Invoice Date" and "Revenue".

Other Table "JobCost" has columns "Date" and "Cost".

I want to plot a visual of "Line and Column Chart". Which have Months on x-axis (Must be from Combination of both Tables Date column) , Y-axis Revenue, Secondary Y-axis Cost.

I tried to combine both Date columns but its giving me an Error. When I am refrring to any one of Date column Vs other Columns, its showing wrong values.

Please guide me, how to solve this problem. I tried many options since last days. Its not working. Guidelines will be really helpful.

Thank you.

1 ACCEPTED SOLUTION
Super User

hi @Prr2023 ,

not sure if i fully get you. supposing you have an invoice table and a cost table like:

try like:
1) add a calculated dates table like
dates = ADDCOLUMNS( CALENDAR( MIN(MIN(invoice[Date]), MIN(cost[Date])), MAX(MAX(invoice[Date]), MAX(cost[Date])) ), "YYMM", FORMAT([Date], "YY/MM") )

2) relate the three tables like:

3) plot a line and column chart with dates[yymm] column, invoice[revenue] and cost[cost] like below:

The point is to have shared dimension from a dedicated dimensional table.

2 REPLIES 2
Regular Visitor

It works. Thank you.

Super User

hi @Prr2023 ,

not sure if i fully get you. supposing you have an invoice table and a cost table like:

try like:
1) add a calculated dates table like
dates = ADDCOLUMNS( CALENDAR( MIN(MIN(invoice[Date]), MIN(cost[Date])), MAX(MAX(invoice[Date]), MAX(cost[Date])) ), "YYMM", FORMAT([Date], "YY/MM") )

2) relate the three tables like:

3) plot a line and column chart with dates[yymm] column, invoice[revenue] and cost[cost] like below:

The point is to have shared dimension from a dedicated dimensional table.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors