Forum Discussion
One date filter for multiple date columns in one table
My first post and apologies as I'm sure I found ther Answer to my question last week but can't find it now !
I'm new to Power BI and trialling it to replace lots of spreadsheets for our Companies KPI Reporting etc.
One fundemental thing I am trying to achive is on the Report have a filter or slicer that allows the report veiwer to select month & year, that then applies for all dates. for example, if I have a table of:
| Order Num | Date Created | Date Delivered |
| 55551 | 28/03/20 | 12/04/20 |
| 55552 | 31/03/20 | 08/04/20 |
| 55553 | 31/03/20 | 16/04/20 |
| 55554 | 04/04/20 | 28/04/20 |
| 55555 | 20/04/20 | 12/05/20 |
| 55556 | 28/04/20 |
On the report I want to be able to select April and 2020 then have to seperate visulisations,
1. Date Created which will show 3 (& info related to those 3)
2. Date Delivered which will show 4 (& info related to those 4)
I've looked at using a calendar table but as both fields are in the same table I can't create a relationship to both fields (I don't think).
I thought the solution I found was create a slicer with Date Created and then add Date Delivered (drag and drop onto the slicer), that would then apply the filter to visuals as those date fields are used in visuals. Is that correct? it appears to work this way but want to check if I'm missing something obvious or if there is a better way?
Thanks
Nick.
NickEccles , you can join both dates one active and another one inactive and use userelation to activate one
Refer this blog how to do
HI NickEccles,
Did your date field succeed recognize as date hierarchy? If this is a case, you can expand the hierarchy fields and extract the year and month fields to create slicers.
If not, you can also check the auto date/time option or create calculated columns with month and year function to extract year and month values form date field.
Apply auto date/time in Power BI Desktop
Function Description YEAR Returns the year of a date as a four-digit integer in the range 1900-9999. MONTH Returns the month as a number from 1 (January) to 12 (December). Regards,
Xiaoxin Sheng
4 Replies
- mahoneypat
Microsoft Employee
You can add a disconnected table with Dates to your model and use that in your slicer(s). You would then have to adapt all your measures to use the selected value ( var slicerdate = selectedvalue(SlicerDate[Date]) and then use it in filter(s) for your measures).
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- amitchandak
Super User
NickEccles , you can join both dates one active and another one inactive and use userelation to activate one
Refer this blog how to do
HI NickEccles,
Did your date field succeed recognize as date hierarchy? If this is a case, you can expand the hierarchy fields and extract the year and month fields to create slicers.
If not, you can also check the auto date/time option or create calculated columns with month and year function to extract year and month values form date field.
Apply auto date/time in Power BI Desktop
Function Description YEAR Returns the year of a date as a four-digit integer in the range 1900-9999. MONTH Returns the month as a number from 1 (January) to 12 (December). Regards,
Xiaoxin Sheng
Thanks all that helped a great deal.
