Forum Discussion

Divya904's avatar
Divya904
Helper III
8 years ago
Solved

Filtering a table with user entered input date

Hi Experts   Please help me to find how we can filter a table based on user entered date.   I have created a new 'Date' table in Power BI which gives me a 'date field'.  I have created a slicer u...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Ensure that the relationship is from the OrderDate column of the Sales Table to the Date column of the Calendar Table.  Also, the relationship status should be active.  Furthermore, you should be able to simplify your formula to:

     

    =if([PostingDate - User Input Date]<0,CALCULATE(Sum('Sales'[Amount ]),[WhatIsTheAge]>=1,[WhatIsTheAge]<31,[Item Status] = "O"),0)

anandav's avatar
anandav
Skilled Sharer
8 years ago

Divya904,

Have you estblished relationship between your fact table and the 'Date' table?

You need to establish the relationship so that the Date slices selction can filter your fact table.

  • Divya904's avatar
    Divya904
    Helper III
    8 years ago

    Hi Anandav

     

    Thanks for replying back.

     

    yes, i have established a relationship between fact table and 'Date' table

     

    Thanks

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Share the link from where i can download your PBI file.

      • Divya904's avatar
        Divya904
        Helper III
        8 years ago

        Hi Ashish

         

        Thanks for your reply.

        Unfortunately i don't have the sample data. I can explain the issue again here.

        1. I have created a Date table in Power BI as follows: 

         

        MyCalenderDate =
  GENERATE (
    CALENDARAUTO(),
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month =  MONTH ( currentDay )
    VAR year =  YEAR ( currentDay )
  RETURN  ROW ( 
    "day", day,
    "month", month,
    "year", year )
  )

        2. After this i have got the 'Date' field . I have created a relationship between MyCalendarDate (Date) and fact table (OrderDate). 

        3.  Created a slicer using Date field. So that a user can input any date.

        4. This report also includes a table which has some measures. For exampleThe DAX calculation 

        Age 1 - 30 = if([PostingDate - User Input Date]< 0, CALCULATE ( Sum('Sales'[Amount ]),  FILTER ('Sales',[WhatIsTheAge] >= 1 && [WhatIsTheAge] < 31 ) ,FILTER('Sales',[Item Status] = "O")),0)

         

        So the requirement here is, When the user provide any input date/select a date in filter, the amount in calculations like Age 1-30 and so on should change. User can input any date from the past.

         

        Hope this gives you more details.

        Thanks for looking into it.