Hi, How to make the report dynamic with two different dates?

[mon]

Hi,

I have created multiple tables in the report. My 'X' table is based on one date " shipped date" and 'Y' table is based on "Return date" . However as i am using two different active relationship with two different tables it is getting hard to make it dyna,ic. Do you guys have any suggestions or advise in this please?

Any kind of help will be appreciated.

Thanks.

[mon]

Hi @amitchandak , @Mikelytics , @MFelix , @Greg_Deckler , @Jihwan_Kim , Can you guys guide me in this please?

Thanks.

[MFelix]

Hi @mon,

Do you have a calendar table to make the relationship between the tables?

[mon]

Hi @MFelix ,

Thanks for the reply. I used common fiscal calender table where i related it with the "shipped date". However, the X table values were coming right. But Y table values were coming wrong as it is dependednt on "returned date" as the returned date is completely different from the shipped date.

Thanks.

[MFelix]

Hi @mon 

What do you mean by the values are coming wrong? Are you using the date tables on your visualizations and filters?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[mon]

Sure @MFelix . Thanks .

[Arul]

@mon ,

To achieve this you have to create common date table and make the relationship from "Date table"[Date column] to "'X' table" [shipped date column] and "Date table"[Date column] to "'Y' table" [Return date column]. Now you can use the dates from Date table that would filter the data from both tables accordingly based on the relationship.
You can refer the video and blog in the below links for date table creation,
https://youtu.be/Pzn2B4laK9A 
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns 

Thanks,

[mon]

Hi @Arul

Thank you so much for your kind reply. However, I need to use two different dates as these two tables are based on two different dates. X is based on Shipped date and Y table is based on Returned date. Do you have any other suggestions how to relate them together? Because previously i used only Shipped date for both of the tables and the values were wrong for the Y table. then found out that the dates are different in Returned date and Y table is dependent on that. But now that i used two different dates, the report is not dynamic anymore. 😞

Kindly advise!

Thanks.