locked
Data filtering using multiple date columns of a table RRS feed

  • Question

  • Hi,

     

    I need some approaches on the below. I have a tabular model with the table called proposals and

    Suppose table proposals have different date columns like initiationdate, responsedate, completeddate.

    I will consider calendar table for the date prompt in report

    And I have date range filter in report where user prompts Fromdate and Todate

    Now when a user selects the range in date fitler in powerbi report, the records to be displayed on the visual is from the table proposals with the multiple conditions like where initiationdate between the date range selected in date filter

    or responsedate between the date range selected in date filter 

    or completeddate between the date range selected in date filter

    productid initiationdate responsedate completeddate
    127 6/5/2017 6/9/2017 6/13/2018
    128 6/6/2017 6/10/2017 6/14/2018
    129 6/7/2017 6/15/2017 6/19/2018
    130 6/8/2017 6/16/2017 6/20/2018
    131 6/9/2017 6/17/2017 6/21/2018
    132 6/10/2017 6/18/2017 6/22/2018
    133 6/16/2017 6/19/2017 6/23/2018
    134 6/17/2017 6/20/2017 6/24/2018
    135 6/18/2017 6/21/2017 6/25/2018
    136 6/19/2017 6/22/2017 6/26/2018
    137 6/20/2017 6/23/2017 6/27/2018
    138 6/21/2017 6/24/2017 6/28/2018

     

    date prompt  
    From To
    6/7/2017 6/14/2017

     

    Expected Output:    
    productid initiationdate responsedate completeddate
    127 6/5/2017 6/9/2017 6/13/2018
    128 6/6/2017 6/10/2017 6/14/2018
    129 6/7/2017 6/15/2017 6/19/2018
    130 6/8/2017 6/16/2017 6/20/2018
    131 6/9/2017 6/17/2017 6/21/2018
    132 6/10/2017 6/18/2017 6/22/2018

    Please let me know the approach/ideas how shall i design the model and come up with the query

     

    Thanks

    Tuesday, January 30, 2018 11:06 AM

Answers

  • Hi amarri,

    Thanks for your question.

    If you have date range filter in powerbi report where user prompts Fromdate and Todate, you can create measure as below DAX formula,please note that you still need to make sure your calendar table is unrelated to your data table:

    FromDate =
    CALCULATE ( MIN ( CalendarDate[Date] ), ALLSELECTED () )
    
    ToDate = 
    CALCULATE ( MAX ( CalendarDate[Date] ), ALLSELECTED () )
    
    Measure =
    IF (
        (
            MAX ( data[initiationdate] ) >= [FromDate]
                && MAX ( data[initiationdate] ) <= [ToDate]
        )
            || (
                MAX ( data[completeddate] ) >= [FromDate]
                    && MAX ( data[completeddate] ) <= [ToDate]
            )
            || (
                MAX ( data[responsedate] ) >= [FromDate]
                    && MAX ( data[responsedate] ) <= [ToDate]
            ),
        1
    )

    For this issue, you can also refer to below Thread:
    http://community.powerbi.com/t5/Desktop/Split-amp-filter-data-from-one-table-into-multiple-based-on-a/m-p/341148#M152751


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 31, 2018 3:17 AM

All replies