Forum Discussion
Filtering Multiple Date Columns in one Report
Hi spamspam
It seems you may try to use DATEDIFF Function to create the measures for each column as requested. For example:
UpdateWorkersComp = IF ( DATEDIFF ( NOW (), MAX ( 'Sample'[Workers comp] ), DAY ) <= 30, MAX ( 'Sample'[Workers comp] ) )Regards,
Cherie
Hi spamspam
It seems you may try to use DATEDIFF Function to create the measures for each column as requested. For example:
UpdateWorkersComp =
IF (
DATEDIFF ( NOW (), MAX ( 'Sample'[Workers comp] ), DAY ) <= 30,
MAX ( 'Sample'[Workers comp] )
)Regards,
Cherie
Hi spamspam
What I understood from your post is - You have 3 columns with dates in it. You want to find out if any of those dates are falling in next 30 days. Presently your method is showing the results of records where all the 3 dates are within next 30 days. Instead you want to expand the selection from ALL to ANY.
I suggest you add one calcualted column to your data model and store the minimum of (Date1,Date2,Date3) in this calculated column. Then base your <30 filter on this new calculated column.
It will work.
Hi Sreenath,
I have 9 date columns and i want a date filter which when filtered should show any records from any of the 9 date columns falling in the date range. Can you help plz?
For ex: For last 1 month; i want to see all the records that fall in this category from all 9 date columns. Basically if every date column has a date that falls in last 1 month, then those records should show. I am not able to find solution to this. Kindly help. Thanks,
Below is the data :
Task# User Task Name Planned Start Planned End Actual Start Actual End IT Approval Initial Approval Technical Approval Final Approval Status 1 John Task 1 10/2/2023 0:00 10/8/2023 0:00 10/2/2023 0:00 10/8/2023 0:00 12/26/2023 0:00 11/4/2023 0:00 12/13/2023 0:00 In Progress 2 Johnny Task2 9/12/2023 0:00 9/27/2023 0:00 9/12/2023 0:00 12/2/2023 0:00 On-hold 3 Claire Task3 On-hold 4 Venna Task4 12/3/2023 0:00 12/3/2023 0:00 12/13/2023 0:00 In Progress 5 Sierra Task5 9/30/2023 0:00 10/4/2023 0:00 Canceled 6 Adam Task6 Completed 7 John Task7 9/24/2023 0:00 9/25/2023 0:00 9/25/2023 0:00 9/25/2023 0:00 Completed 8 Johnny Task8 11/6/2023 0:00 11/8/2023 0:00 11/6/2023 0:00 11/11/2023 0:00 11/12/2023 0:00 11/19/2023 0:00 Completed 9 Claire Task9 9/9/2023 0:00 9/11/2023 0:00 9/9/2023 0:00 9/11/2023 0:00 10/1/2023 0:00 10/1/2023 0:00 Completed 10 Venna Task10 12/23/2023 0:00 1/3/2024 0:00 12/23/2023 0:00 1/3/2024 0:00 1/14/2024 0:00 1/7/2024 0:00 1/17/2024 0:00 Completed Hi sizi
In Power Query, I unpivoted the date columns.
I also added 2 dimension tables (Date and Phase) and created 1:* relationships between the dimension tables and the fact table.
Filtering Multiple Date Columns in one Report - 1.pbix
Let me know if you have any questions.
I cannot unpivot the date columns in power query. its giving error. My datasource is sharepoint. Is there any other way i can define date filter which can filter dates based on any date column?
Thanks,
