One date filter for multiple date columns in one table

[NickEccles]

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.

[amitchandak · Accepted solution]

@NickEccles , you can join both dates one active and another one inactive and use userelation to activate one

Refer this blog how to do

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[NickEccles]

Thanks all that helped a great deal.

[Anonymous · Accepted solution]

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

[amitchandak · Accepted solution]

@NickEccles , you can join both dates one active and another one inactive and use userelation to activate one

Refer this blog how to do

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[mahoneypat]

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