error showing data of calculated measures from two tables on one chart

[xangetsu21]

i have a power bi report from two tables. the tables are different sales tables logging sales performance of every product on everyday. both two tables have a date column. and both of them have a sales amount column and a quantity column. i have two measures to calculate the total revenue by multiplying the qty and amount named revenue1 and revenue2. and i wanted to see the daily chnage in revenue, so i added two measures revenue1 prev day and revenue2 prev day to calculate the final two measures change in revenue1 and change in revenue2 by subtracting revenue from revenue prev day. the code for the prev day is like this:

revenue1 prev day = VAR CurrentDate = SELECTEDVALUE('sales1'[DATE])

                      VAR LastWorkingDay=

                          CALCULATE(

                             MAX('sales1'[DATE]),

                             FILTER(

                                ALL('sales1'),

                                'sales1'[DATE]<CurrentDate

                             )

                          )

                        RETURN

                           IF(

                            NOT(ISBLANK(LastWorkingDay)),

                            CALCULATE([revenue1],'sales1'[DATE]=LastWorkingDay),

                            BLANK()

                           )

this measure calculate the prev days revenue and it takes the last avilable date on table neglecting the prev day if it was holiday and thus absent on the table. both the measures for prev day revenue and change in revenue work fine for each table.

but i wanted to see both changes in revenue from the two tables at one chart at once where i am facing the problem. first i tried making a many to many relationship between two tables on the date column. and by doing this when i put both revenue change measures on one chart and put date from table1 on x axis, it doesnt show the correct value for revenue change2 which is from other table. it only shows whats the revenue2 on today. similarly when i put date of table2 on x axis it shows the same error for revenue change1. its not calculating the prev days revenue measure for the other table.

i tried making a new date table with dax code from the date column of table1 since it goes further bakc on calendar than table2. and replacing the salesX'date' with the new date table'date' on the revenue prev day measures but its not working. 

can any one tell me how to solve this? how to show the two revenue change measure on a same date axis correctly? and the two tables cant be merged as they contain different sorts of products and have different columns from each other except the date, quantity and sales amount column and thats how its needed for many other charts. please help, any suggestion is greatly appreciated

[Kedar_Pande]

@xangetsu21 

Create a Date Table:

DateTable = CALENDAR(MIN('sales1'[DATE]), MAX('sales2'[DATE]))

Create Relationships:

DateTable[Date] → sales1[DATE]
DateTable[Date] → sales2[DATE]

Update Measures

revenue1 prev day = 
VAR CurrentDate = SELECTEDVALUE(DateTable[Date]) // Use the date from DateTable
VAR LastWorkingDay =
CALCULATE(
MAX('sales1'[DATE]),
FILTER(
ALL('sales1'),
'sales1'[DATE] < CurrentDate
)
)
RETURN
IF(
NOT(ISBLANK(LastWorkingDay)),
CALCULATE([revenue1],'sales1'[DATE] = LastWorkingDay),
BLANK()
)

You would do something similar for the revenue2 prev day measure, ensuring it also references DateTable[Date].

Create Change in Revenue Measures:

Change in Revenue1 = [revenue1] - [revenue1 prev day]
Change in Revenue2 = [revenue2] - [revenue2 prev day]

Now, create a line or bar chart. Use the DateTable[Date] for the X-axis. Drag both Change in Revenue1 and Change in Revenue2 measures onto the Values field of the chart.

If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn

[xangetsu21]

i already tried this as in the post i said i made a new date table with dax code. i made the one to many relationship and changed the previous day revenue measure with what you suggested. but this doesnt show any value at all in the chart when placed against the date column from new date table. do you know any other trick or what might be the problem?

[Kedar_Pande]

share the file, please. upload in drive and share link here

[xangetsu21]

hey did you get to check my file? i shared the link to the file. and please inform me whether you do or do not know the solution for this. thanks

[xangetsu21]

okay i made a small version of the report for sharing purpose. the actual names are a bit different from what i described here, i think you would neglect this. the stated visual is the second chart on page 1 and i left some other visuals to show the codes are working on other charts and for taking a look at actual values real quick. here is the link:

https://drive.google.com/file/d/1YndDKyuC_Do0F5wLLAkCXiGKHz7NFgJ4/view?usp=sharing

[rajendraongole1]

Hi @xangetsu21 - create a common Date Table and bring the relationships to both sales1 and sales2, the date context is synchronized across both tables.
The Prev Day measures are updated to use the same DateTable[Date], ensuring consistency when calculating previous day revenue across both tables.
The final result allows you to plot both Change in revenue1 and Change in revenue2 on the same chart using the shared date axis.

Link the DateTable[Date] column to the DATE column in both sales1 and sales2. This will be a one-to-many relationship from DateTable to each sales table.

DateTable = CALENDAR(MIN('sales1'[DATE]), MAX('sales1'[DATE]))

create a measure 

revenue1 prev day =
VAR CurrentDate = SELECTEDVALUE('DateTable'[Date])
VAR LastWorkingDay =
CALCULATE(
MAX('sales1'[DATE]),
FILTER(
ALL('sales1'),
'sales1'[DATE] < CurrentDate
)
)
RETURN
IF(
NOT(ISBLANK(LastWorkingDay)),
CALCULATE([revenue1], 'sales1'[DATE] = LastWorkingDay),
BLANK()
)

For revenue2 prev day

revenue2 prev day =
VAR CurrentDate = SELECTEDVALUE('DateTable'[Date])
VAR LastWorkingDay =
CALCULATE(
MAX('sales2'[DATE]),
FILTER(
ALL('sales2'),
'sales2'[DATE] < CurrentDate
)
)
RETURN
IF(
NOT(ISBLANK(LastWorkingDay)),
CALCULATE([revenue2], 'sales2'[DATE] = LastWorkingDay),
BLANK()
)

You can now calculate the change in revenue

Change in revenue1 = [revenue1] - [revenue1 prev day]

Change in revenue2 = [revenue2] - [revenue2 prev day]

Hope the above calculation helps

[xangetsu21]

i already tried this as in the post i said i made a new date table with dax code. i made the one to many relationship and changed the previous day revenue measure with what you suggested. but this doesnt show any value at all in the chart when placed against the date column from new date table. do you know any other trick or what might be the problem?