Forum Discussion

gregormc's avatar
gregormc
New Member
1 year ago

Date Slicer

I have a requirement for a page on a dashboard where the user selects a date range from a slicer set to between (DateTable [Date]) and the output from that will populate a table visual with any rows that have a start date or end date within the selected date range i.e. 01/12/2024 - 10/12/2024.

 

I have tried the following: Filtered Rows =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
RETURN
IF(
NOT(ISBLANK(SelectedDate)),
CALCULATE(
COUNTROWS(Table1),
FILTER(
Table1,
(Table1[StartDate] > SelectedDate) ||
(HL3_Tenant_Details[Moved Out Date] < SelectedDate) ||
(ISBLANK(HL3_Tenant_Details[Moved Out Date]) && HL3_Tenant_Details[Moved In Date] > SelectedDate)
)
),
0
)

 

The MIN date of the table is continually returning 18/11/024 00:00:00 although this should be 01/11/2024.

 

I am absolute miles off a solution here and need some assistance

Need Help

3 Replies

  • govind_021's avatar
    govind_021
    Super User
    1 year ago

    Hey , gregormc
    If Date Table is Connected to your Main Table and You are using Date Slicer by Date Field in the Date table
    then you does not need to write a measure because you have direct relationship b/w date and data table.
    It will populate the filters to the table visual and should always show the data for dates b/w selected date range.

    Best Regards,
    Govind Sapkade
    Data Analyst | Power BI Enthusiast | Microsoft PL 300 Certified Power BI Data Analyst | MS Fabric Enthusiast
    🎥Subscribe to my youtube channel for hands on tutorials : Youtube

    📊Let’s connect on Linkdin : Linkdin

  • mickey64's avatar
    mickey64
    Super User
    1 year ago

    How about this formula?

     

    Filtered Rows =
    VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
    RETURN
    IF(
    NOT(ISBLANK(SelectedDate)),
    CALCULATE(
    COUNTROWS(Table1),
    FILTER(
    ALL(Table1),
    (Table1[StartDate] > SelectedDate) ||
    (HL3_Tenant_Details[Moved Out Date] < SelectedDate) ||
    (ISBLANK(HL3_Tenant_Details[Moved Out Date]) && HL3_Tenant_Details[Moved In Date] > SelectedDate)
    )
    ),
    0
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi gregormc ,

    Is this issue solved now? If you have any questions, please feel free to share with us. ðŸ™‚

    If you have any confused, could you please provide example data or sample files here  We could offer you more help if we have information in detail. And what you expect the output to be. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

    Best regards,

    Lucy Chen