Filtering on 2 date fields in a table

[Anonymous]

Hi

I'm trying to write a report that contains 2 table with one slicer that filters on financial year and month.  The first table will contain a list of all the inspections created within the month selected in the slicer for example all the inspections created in May 2018 The second table contains the a list of all the inspections created within the month selected in the slicer for example all the inspections completed in May 2018.

An example of the data is below:

No.                    Account no          created date               Inspection type             completed date

1	6767	31/05/2018	normal	05/06/2018
2	1960	28/03/2018	normal	02/04/2018
3	3122	22/02/2018	normal	27/02/2018
4	2198	22/01/2018	normal	27/01/2018
5	2465	21/05/2018	advnaced	26/05/2018
6	2047	25/05/2018	normal	30/05/2018
7	3507	06/06/2018	advnaced	11/06/2018
8	1184	05/03/2018	normal	10/03/2018
9	3872	08/06/2018	advnaced	13/06/2018
10	1757	09/02/2018	advnaced	14/02/2018
11	4507	31/05/2018	advnaced	05/06/2018
12	2318	20/01/2018	normal	25/01/2018
13	1897	24/02/2018	advnaced	01/03/2018
14	4109	07/01/2018	advnaced	12/01/2018
15	3044	31/05/2018	advnaced	05/06/2018
16	2202	12/05/2018	advnaced	17/05/2018
17	2663	04/05/2018	advnaced	09/05/2018
18	3199	10/01/2018	normal	15/01/2018
19	3987	18/02/2018	advnaced	23/02/2018
20	1872	18/02/2018	advnaced	23/02/2018
21	3460	11/05/2018	advnaced	16/05/2018

I have a date table and the date is linked to the completed date in my the inspection table.  I can created the completed table OK but when I try to create the created inspections table I don't get the correct results.  I have created an inactive relationship from the datetable to created date but I am unsure how to create a DAX calculation to use this.

Many thanks in advance

[Anonymous]

try this:

[Measure that uses inactive relationship] =
CALCULATE (
    [Same Measure that the active relationship uses],
    USERELATIONSHIP ( DateTable[DateColumn], FactTable[created date] )
)

[Anonymous]

Hi

that's great thanks and helps sum up the number of created inspections.  What i need to do is show a list of created inspections and a list of closed inspections on the same report page using the same filters - similar to this.  At the moment both reports show the same inspections just with the created date or completed date Any ideas?  Thanks for your help.

[Ashish_Mathur]

hi @Anonymous,

Are these your expected results?

[Anonymous]

yes @Ashish_Mathur!  I  would be very grateful if you could share how you have done this.

Many thanks

Jo.

[Ashish_Mathur]

Hi,

You may refer to my solution at this link.

Hope this helps.

[Anonymous]

Hello Thank you for your help. I have downloaded your example but unfortunately cannot open the file. Please can you post the example pbix file please? Kind regards Jo.

[Ashish_Mathur]

Hi,

It will be the same link that i will share here as well.  There is no provision to upload files here.

[Anonymous]

Hello

Thank you I have managed to download the pbix file now.  Please can you walk through what you have done.  I am a little confused where the a custom column has been created.

Many thanks

Jo

[Ashish_Mathur · Accepted solution]

Hi,

It will take a lot of time for me to walk you through all that i have done.  To answer your specific question, the Custom column i created has converted two date columns into a single one by exploding each data of the date range into a single row.  This has been done so that we can relate this single column to the Date column of the Calendar Table.

Please feel free to ask any specific questions that you may have.

[Anonymous]

@Ashish_Mathur apologies for the delay in replying to your message.  I have looked at your solution in detail and thank you for responding to my question.  I have applied your method to my data and all has worked.  Thanks again for taking the time to answer my question.

Kind regards

Jo

[Ashish_Mathur]

You are welcome.

[Anonymous]

change the tablename[create date] to [completed date] in the USERELATIONSHIP() function.