Forum Discussion

spamspam's avatar
spamspam
Regular Visitor
7 years ago
Solved

Filtering Multiple Date Columns in one Report

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 

  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

10 Replies

  • spamspam's avatar
    spamspam
    Regular Visitor
    7 years ago

    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's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      spamspam
      Regular Visitor
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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