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 ...
  • 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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.