Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 using 'Date' field in my report.
Now when the user selects a single date in the slicer, my table should get filter according to that.
There are some DAX calculations in the table like Total Sales and some DAX buckets.
Thanks in advance.
Regards
Solved! Go to Solution.
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)
@Divya904, Can you share the end result that you are expecting along with some dummy data
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.
Hi Anandav
Thanks for replying back.
yes, i have established a relationship between fact table and 'Date' table
Thanks
Hi,
Share the link from where i can download your PBI file.
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.
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)
Hi Ashish
Thanks for the reply.
The relationship has been set before.
In the following Measure:
if([PostingDate - User Input Date]<0,CALCULATE(Sum('Sales'[Amount ]),[WhatIsTheAge]>=1,[WhatIsTheAge]<31,[Item Status] = "O"),0)
Just wanted to confirm, when the user inputs a date in the filter, will it go in the above measure calculation and this calc will work accordingly.
Or there is any other way to filter around the user input date.
Thanks
Hi,
Yes, the measure should recalculate when the user select a certain date in the filter.