Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filtering on 2 date fields in a table

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

1676731/05/2018normal05/06/2018
2196028/03/2018normal02/04/2018
3312222/02/2018normal27/02/2018
4219822/01/2018normal27/01/2018
5246521/05/2018advnaced26/05/2018
6204725/05/2018normal30/05/2018
7350706/06/2018advnaced11/06/2018
8118405/03/2018normal10/03/2018
9387208/06/2018advnaced13/06/2018
10175709/02/2018advnaced14/02/2018
11450731/05/2018advnaced05/06/2018
12231820/01/2018normal25/01/2018
13189724/02/2018advnaced01/03/2018
14410907/01/2018advnaced12/01/2018
15304431/05/2018advnaced05/06/2018
16220212/05/2018advnaced17/05/2018
17266304/05/2018advnaced09/05/2018
18319910/01/2018normal15/01/2018
19398718/02/2018advnaced23/02/2018
20187218/02/2018advnaced23/02/2018
21346011/05/2018advnaced16/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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    try this:

     

    [Measure that uses inactive relationship] =
CALCULATE (
    [Same Measure that the active relationship uses],
    USERELATIONSHIP ( DateTable[DateColumn], FactTable[created date] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

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