Filtering Multiple Date Columns in one Report

[spamspam]

Hi All,

I have been using Powerbi for a few months, report writing ect and i am stuck on this.

I have 3 date columns, for different types of Insurances, i want to be able to see any that are expiring in the next 30 days.

So iam using the Grid i can see my 3 Insurance Dates, but the data displaying is only of all 3 of the columns are in the next 30 days, i would like to display if any of the dates are due in the next 30 days. I am currently filtering on the pages level filters (Is in the next 30days)

Any help to make it pull from each column? and show nothing if not

Cheers 

[spamspam]

Hello,

          New to PowerBi, I have 3 date fields, they are different types of insurances,with a company name attached to the date field and i need to see the expiery in the next 30 days.

They are currently in the table visual, and i have setup page level filters, Relatative Date Filtering  "IS in the next 30 Days"

My problem it is only showing data if all 3 dates are expiering in the next 30 days, how can i make it show data from each indivual date column, and if say 2 date column 2 and 3 are not out it is just blank.

Cheers In Advance

[v-cherch-msft]

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

[spamspam]

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

[v-cherch-msft · Accepted solution]

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

[spamspam]

Well that now seems to be working thanks for that i can now expand further on this

Great work thanks for your help!

[Anonymous]

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.

[sizi]

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

[gmsamborn]

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.

[sizi]

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,

[gmsamborn]

Hi @sizi 

To unpivot the date columns, I selected [Task#], [User], [Task Name] and [Status] and then right-click and select "Unpivot Other Columns".

If this doesn't work, please let me know what the error was.