Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI Filter a Date column by another Date Column

I am new to Power BI and have a specific report request. My base table looks like below.

 

Requirement #1 : Whenever, end user selects a specific 'Business Date', then the 'Due Date' which on or after that selected 'Business Date' should only get displayed. All the other older due dates shouldn't appear on the visual.

So far, I am able to store the 'Business Date' value what end user enters using 'SELECTEDVALUE()' function. But Power BI doesn't allow me to filter records by another column in Advanced Filtering.

 

Requirement #2 : Store the difference between selected 'Business Date' and all other future 'Due Date' like shown below. So that bucketing of days can be done and dispLay the bucket on visual.

 

Please help me achieve it. GilbertQ Anonymous 

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    create as column
    Days diff = datediff([Business Date],[Due day], day)
    Create a bucket on top of if

    Have slicer on Business Date.
    Create measure

    loan =
    var _max = maxx(table,table[Business Date])
    return
    CALCULATE(count(table[Loan]),[Due day] >=_max)

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    To achieve the second requirement you can add a simple column like the one below

    Day Diff = 
VAR __dayDiff = 'Table'[Due Date] - 'Table'[Bussiness_Date] 
RETURN IF( __dayDiff >= 0, __dayDiff )

    For the first requirement, the table visual or any other visual can not propagate itself, therefore, you can use another visual or slicer to select Bussiness date and add your newly created column to the Filter Panes / Filters on this visual section with a setup as below.

     
    Please see the attached file wit a solution for reference.
     
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    create as column
    Days diff = datediff([Business Date],[Due day], day)
    Create a bucket on top of if

    Have slicer on Business Date.
    Create measure

    loan =
    var _max = maxx(table,table[Business Date])
    return
    CALCULATE(count(table[Loan]),[Due day] >=_max)

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    To achieve the second requirement you can add a simple column like the one below

    Day Diff = 
VAR __dayDiff = 'Table'[Due Date] - 'Table'[Bussiness_Date] 
RETURN IF( __dayDiff >= 0, __dayDiff )

    For the first requirement, the table visual or any other visual can not propagate itself, therefore, you can use another visual or slicer to select Bussiness date and add your newly created column to the Filter Panes / Filters on this visual section with a setup as below.

     
    Please see the attached file wit a solution for reference.
     
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous has the above replies helped. if you need more help make me @

    Appreciate your Kudos.