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.

     

parry2k's avatar
parry2k
Super User
4 years ago

Anonymous  jdbuchanan71 has provided a great solution but if I have to do this I will unpivot the table (which is always a best practice) and then I have one relationship with date table and I can slice on any date and also on any date column. 

 

To achieve this:

 

Transform Data -> Select ID column -> Right Click -> Unpivot other columns

 

you will get two columns, Attribute and Value, rename these as you see fit, the attribute will have column names, and value will be date.

 

Close and apply.

 

Set relationship between date table and transaction table, and from here onward everything should be straightforward.

 

 

✨ Follow us on LinkedIn and  to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.