topic How to Plot Date (Month) Vs Revenue Vs Cost? Date columns must combine from 2 different Tables in DAX Commands and Tips

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

Anonymous — Mon, 13 Nov 2023 20:55:02 GMT

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.

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

FreemanZ — Tue, 14 Nov 2023 05:37:59 GMT

hi Anonymous ,

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.

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

Anonymous — Tue, 14 Nov 2023 08:50:45 GMT

It works. Thank you.