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 'Unpivot other columns' in query editor. Then filter the date column as below. If it is not your case, please share some simplified data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.
How to Get Your Question Answered Quickly
Regards,
Cherie
Hi Cherie,
Thanks for the reply, Here is my Insurances due in 30 days screen shot
To the left if these is just company name. These dates are in the same table
Data is only pulling through if any of the Workers Comp/Public or Motor match the condition of 30 days
I am expecting alot more data, with some being blank because the expiry is in date.
I just have page filters setup on each date column "Is in the next 30 days"
Hope this helps to show what iam missing
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
Well that now seems to be working thanks for that i can now expand further on this
Great work thanks for your help!
