How to link 2 date cols i.e. Opened & Closed Date of one table with 1 date column of calendar table

[vikrammankar]

Hi,

I have a Power BI File with "Calendar" table with Date Column & another table called "Tbl_INC_SR_CR_Tickets_Query" with columns "Opened_Date", "Closed_Date" & "CarriedForward_Date".  Relationship in Calendar Table & "Tbl_INC_SR_CR_Tickets_Query" is on "Opened_Date".  I have a page with 1 Slicer which has months from "Calendar" table, & another table visual in which i want to show how many tickets are opened in the month which is selected from slicer, how many tickets are closed in the month which should also be selected from the same slicer, & also how many tickets are Carried Forward in the month which should also be selected from the same slicer.  So basically 1 single slicer should be able to show Tickets Opened, Closed & Carried Forward. I have created relationship between tables "Calendar"(Date Col) & "Tbl_INC_SR_CR_Tickets_Query" ("Opened Date").

Best Regards,
Vikram.

[MFelix · Accepted solution]

Hi @vikrammankar,

You need to have an active and a inactive relationship. 

Then on the column that has the inactive relationship then you need to use the function USERELATIONSHIP to calculate based on the inactive relationship

Something like

Closed = CALCULATE ( COUNT( TABLE[COLUMN] ), USERELATIONSHIP(TABLE[CLOSEDDATE], CALENDAR[DATE] ) ) 

[MFelix · Accepted solution]

Hi @vikrammankar,

You need to have an active and a inactive relationship. 

Then on the column that has the inactive relationship then you need to use the function USERELATIONSHIP to calculate based on the inactive relationship

Something like

Closed = CALCULATE ( COUNT( TABLE[COLUMN] ), USERELATIONSHIP(TABLE[CLOSEDDATE], CALENDAR[DATE] ) ) 

[Yifatgo]

Hi @MFelix 

I also have this issue. It works great when using the count and userelationship filter, but what I couldn't find is how to show the information on a table visual. 

For example, I want to have the ability to filter dates (using the dates column from the date table), but to also show on different tables:

1. What has been opened in that date range,

2. What has been closed in that date range.

Can you please advise how to do so? thanks! 

[MFelix]

Hi @Yifatgo

Not sure if I understand the request but in whatever vvisualization you are using you must use the calendar table alongside with your metrics not the original dates.

How are you setting your tables? 

[Yifatgo]

Hi Miguel Félix! Thanks for the fast reposed, I will try to explain better:

I have a list of tickets with their creation date ("Short Date_Created", date format), some of the tickets were completed, so these also have a completion date ("Short Date_Completed", date format).
Both dates are linked to a general date table (TimeTable); The active relationship is between created date and the general date, the inactive is for the completion date (I marked them on the picture).

I'm trying to analyze the data, as a start - created vs. completed tickets. 

The count was simple, as I used calculate function with count and userelationship (two measures, one per created and one per completed, with the relevant relationship). But I also need to show what was created and what was completed in the date range.

The date slicer is taken from the TimeTable, and as I can see good results for the created tickets (comparing these to the raw data that I have), I cannot find a way to present the completed tickets list (assuming it's because of the inactive relationship).

For example: I see 15 created tickets on Feb with the correct list of tickets. 12 tickets were completed on Feb, but the list doesn't filter them correctly, it just shows the created issues.

So my question is - how can I show them on a table? I want to present the "12" that pbi was able the count 😅 I hope that was clearer. 

Again, thanks for taking the time to assist! @MFelix 

[MFelix]

Hi @Yifatgo ,

Believe your issue is related with the bi directionality of your filters that when you get the context filtered it return the incorrect results,

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.

[Yifatgo]

Hi @MFelix,

I managed to solve it (not sure if it's elegant way, but it suits me!), wrote about it here:

https://community.powerbi.com/t5/Desktop/Counts-via-userelationship-works-but-it-doesn-t-show-the-ri...

Thanks again for your help! 

[vikrammankar]

Hi Miguel,

I just noticed.  This solution is working perfectly fine for Closed Date.  However it is not working for Carried Forward Date. 

I have created 1 active relationship between:

"TblCalendar(Date)" & "Tbl_INC_SR_CR_Tickets_Query (Opened_Date)". 

I have also created 2 inactive relationships between:

a)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (Closed_Date)"

b)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (CarriedForward_Date)"

I have created two measures:

a) Closed_MMM_YYYY1 = CALCULATE ( COUNT( Tbl_INC_SR_CR_Tickets_Query[Number] ), USERELATIONSHIP(Tbl_INC_SR_CR_Tickets_Query[Closed_MMM-YYYY], Tbl_Calendar[Date] ) )

b) CF_MMM_YYYY1 = CALCULATE ( COUNT( Tbl_INC_SR_CR_Tickets_Query[Number] ), USERELATIONSHIP(Tbl_INC_SR_CR_Tickets_Query[CF_MMM-YYYY], Tbl_Calendar[Date] ) )

Am I doing anything wrong ?

Best Regards,
Vikram.

[vikrammankar]

Hi Mfelix,

Awesome......You just made my day.  This is exactly what I wanted.  Hats Off to U.  I was struggling with this for a week.  Thanks a millionnnnnnnnn....

🙂

I have one more query.  Mentioning it below.  Will be really very greatful, If you can help...

I have two tables,

1. Calendar

2. TblSales with 3 columns i.e. Date, Country & Sales Amount (with 3 months data in it.)

I have a clustered column chart in which I have put in Shared Axis,

"Date Hierarchy" from Calendar Table &

Country from "TblSales" Table.

In Column Values I have put Sales Amount from "TblSales" Table.

I want to sort this chart first on date as ascending and then on Sales Amount as descending.  I am able to achieve this in table visual, however I am unable to achieve it in chart visual.

Mentioned below is the sample data for TblSales. 

Date                 Country      Sales Amount
01-Jan-2021    US              100
01-Jan-2021    UK              150
01-Jan-2021    UAE            200
01-Feb-2021    US             500
01-Feb-2021    UK             450
01-Feb-2021    UAE           300
01-Mar-2021    US             820
01-Mar-2021    UK             850
01-Mar-2021    UAE           700

Best Regards,

Vikram.

[vikrammankar]

Hi Miguel,

I just noticed.  This solution is working perfectly fine for Closed Date.  However it is not working for Carried Forward Date. 

I have created 1 active relationship between:

"TblCalendar(Date)" & "Tbl_INC_SR_CR_Tickets_Query (Opened_Date)". 

I have also created 2 inactive relationships between:

a)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (Closed_Date)"

b)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (CarriedForward_Date)"

I have created two measures:

a) Closed_MMM_YYYY1 = CALCULATE ( COUNT( Tbl_INC_SR_CR_Tickets_Query[Number] ), USERELATIONSHIP(Tbl_INC_SR_CR_Tickets_Query[Closed_MMM-YYYY], Tbl_Calendar[Date] ) )

b) CF_MMM_YYYY1 = CALCULATE ( COUNT( Tbl_INC_SR_CR_Tickets_Query[Number] ), USERELATIONSHIP(Tbl_INC_SR_CR_Tickets_Query[CF_MMM-YYYY], Tbl_Calendar[Date] ) )

Am I doing anything wrong ?

Best Regards,
Vikram.

[MFelix]

Hi @vikrammankar ,

Looking at the expressions all looks good.

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.

[vikrammankar]

Hi Miguel,

I have made a sample file.  Can you share your email id.  I will send the file on it.  I dont know how to upload on Google Drive or One Drive and make it available to you.

Best Regards,

Vikram.

[MFelix · Accepted solution]

Hi @vikrammankar ,

Has refered on the e-mail you need to have both columns with the same format in this case date.