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 

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.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you again jdbuchanan71 ! This works great! I tested it on a few small date-ranges on my original dataset, but noticed something weird after I included the maximum date range. The total count was slightly off. After putting the measure in a table and exporting the data to Excel to do a count there, the total given in Excel is correct but somehow the number displayed in PBI is wrong. 

 

So this appears to be a visual bug and I will look further into. If you happen to have some sources that I can start with to resolve this issue, that would be very much appreciated. I will mark your reply as the solution. Thank you again.