Using the dates from date slicer in queries to filter data for visualization

[BIWhiz]

Hi All,

I am a newbie to Power BI, I need some help trying to build a map dashboard with date slicer. I need to be able to utilise the start and end dates selected in the date slicer and use them as parameters to filter the data from my queries or somehow apply those filters in Power BI.

eg. I have a HR database with Hire Date and Termination Date and I am creating a visualization based on the number of employees active in the period selected by user and analyze their leaves, pay , overtime  in the period selected from a database.

I need to be able to fetch the records where Hire Date <= End date selected in the date slicer  and Termination date is either NULL or >= start date selected in date slicer. I am utilizing a date dimension table for the slicer and have created measures start date as MIN date and end date as MAX date from slicer.

Any ideas?

[jsbi81]

Hi,

Did you find a solution. I'm trying to show the active current asat date. In SQL and Tableau I'm able to achieve using the below where [Date] is my Parameter

[Start Date] <= [Date] AND IFNULL([End Date],[Date]) >= [Date]

Thanks

Jag

[electrobrit]

@BIWhiz

Were you able to solve this? I am having same issue. 

I need to know based on the date selected by the user the active stores. They have termination dates and anticipated active dates so being able to see how many are active as of a date is important.

[BIWhiz]

Hi,

I can tell you what my findings were, I had a hard time figuring this out as Power BI apparently doesnt work like SSRS or most other reporting tools. the only way I could work around this was by flattening my dataset. I joined my dataset with a calendar dimension and generated one row of an entity for each monthyear that entity was to be filtered for and a column with monthyear. Then I set the dateslicer in Power BI with that monthyear column which allowed slicer to filter records for each monthyear.

If the relationships are set right with all other datasets, then the slicer will filter data for everything. Mostly I found it was the relationships not set correctly which caused data to not filter correctly once the base data was all set correctly.

Hope this helps.

[BIWhiz]

Clarifying on the question -

Correlating it with paramterised SSRS dashboards, where user has the ability to change the dates as required and the underlying queries fetch data based on those parameters, Is it possible to have similar ability for user in Power BI?

Select date range from date slicer and use the start and end date in underlying queries as there are multiple date fields in queries.

Any advise will be appreciated.

[v-caliao-msft]

@BIWhiz,

Slicer no works like Parameter that works in SSRS. Slicer only effect on Visual level. We cannot use slicer items in dataset or query. What you can do is use Power BI parameter in query. Please refer to the link below to see the details.
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

Regards,

Charlie Liao

[BIWhiz]

@v-caliao-msft

Thank you for your response.

I explored the parameters option , however I haven't been able to figure how how parameters can be changed by the user and the report/dashboard to show the related data.

Essentially. I am trying to use start date and end date (enterable by user) to filter rows in query based on conditions eg. Start date = '2017-05-01' and End Date '2017-05-31' then I need my queries to use these dates as filters - select [Empname],[EmpHireDate],[EmpTermDate] from Employee where EmpHireDate <='2017-05-31' and   ( EmpTermdate IS NULL or EmpTermdate > '2017-05-31')

this will give me the active employees in that date range.

I did find a workaround though, it was not quite hard to implement. Any ideas will be appreciated.

[Aasish]

Hi @BIWhiz, Can you please let me know what was the work around you implemented ?

[BIghost]

Good Day,

I am also in this same situation - that I need to make use of the

'start date'  and 'end date' in my queries for different date fields.

I will appreciate a feedback on this work-around. Also, Power BI should make the slicer componts' values usable in underlying queries. This desirable feature should be impletemented.

Please keep me posted.

[BIghost]

Good Day,

I am also in this same situation - that I need to make use of the

'start date'  and 'end date' in my queries for different date fields.

I will appreciate a feedback on this work-around. Also, Power BI should make the slicer componts' values usable in underlying queries. This desirable feature should be impletemented.

Please keep me posted.

[v-caliao-msft]

@BIWhiz,

Do you have large data? If not, you can use a slicer instead.

Regards,

Charlie Liao