Reg-how to use different two date fields in two different table

[gladies123]

Hi,

I have a two different table that is table A and table B. i need to use two date field from two different tables but if i enable relationship for that dates Values will be changed. but the thing is if i use revenue date field as slicer  the revenue alone will   change and if i use date as slicer the CTC alone will  change but in tis case i need both revenue and CTC  have to change wiith respect to date which is common to Revenue and CTC

Thanks in Advance

[v-zhenbw-msft · Accepted solution]

Hi @gladies123 ,

If you want to use one date slicer to control two tables, you need to create a date table and create relationships with other tables based on date.

We can use two ways to meet your requirement, one creates a measure, another creates a column in Table B.

1. Create a date table and create the relationships.

Date = CALENDAR("2020/5/1","2020/6/30")

2. Then create a slicer using it.

The measure solution is to create a measure like this,

Measure = 
CALCULATE(SUM('Table A'[Revenue]),FILTER('Table A','Table A'[S.no]=MAX('Table B'[S.no]) && 'Table A'[Revenue Date]=MAX('Table B'[Date])))

The column solution is to create revenue column in Table B.

Column = 
CALCULATE(SUM('Table A'[Revenue]),FILTER('Table A','Table A'[S.no]='Table B'[S.no] && 'Table A'[Revenue Date]='Table B'[Date]))

The final result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, PBIX as attached.

[amitchandak]

@gladies123 , You can use a common date table and join with both the tables and use it?

same way common table for the person and Sno, if needed

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.