Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using a date filter to capture rows within a date range, when multiple date columns are involved

Hello everyone,    First time posting here and new to PBI Desktop. My problem relates to dates and filters. I have searched and looked at several links, but I could not find one that match my parti...
Need Help
Show and Tell
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Anonymous 

    I changed our filter formula a bit to give us a 1 on each return row.

    Dates Filter = 
    VAR _Request =  CALCULATE ( COUNTROWS ( Data_Table ) )
    VAR _Quote =    CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Quote_Issued] ) )
    VAR _Charge =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Issued =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[A_Issued] ) )
    VAR _Sent =     CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[B_Sent] ) )
    VAR _Received = CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Updated =  CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Last_Updated] ) )
RETURN IF ( NOT ISBLANK ( _Request + _Quote + _Charge + _Issued + _Sent + _Received + _Updated ), 1 )

     

    Then we can use it in a SUMX to get the count of records that match.

    Record Count = SUMX ( ALL ( Data_Table[ID] ), [Dates Filter] )

    I have attached my updated file.

     

jdbuchanan71's avatar
jdbuchanan71
Super User
4 years ago

Anonymous 

One way to do it is to create a relationship between your Dates table (every model should have a single master dates table) and every one of the date fields.  Only one of them can be active but we can active the others in a measure.

In my example the relationship to the Request_Date is the active one but that is just becuase it was the first one in the table.

Then we write a measure to count the rows for each of the date fields.

 

 

Dates Filter = 
    VAR _Request =  COUNTROWS ( Data_Table )
    VAR _Quote =    CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Quote_Issued] ) )
    VAR _Charge =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Issued =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[A_Issued] ) )
    VAR _Sent =     CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[B_Sent] ) )
    VAR _Received = CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Updated =  CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Last_Updated] ) )
RETURN _Request + _Quote + _Charge + _Issued + _Sent + _Received + _Updated

 

USERELATIONSHIP activates the link between the Dates table and the Data_Table for that particular field.  The first one  (_Request) does not need it becuse that is the primary active relationship.

 

When we put that measure in the table and applya filter on the Date table it will return only for rows where atleast one of the fields fall into the date range.

I have attached my sample file for you to look at.