Date slicer field

[rodbeck]

Hi,
I have a built a simple reporting from an Excel action log.

The goal was to copile a monthly reproting with

- A chart displaying late actions versus non late actions

- A table displaying all the actions currently in progress

- A table displaying all the actions created since the last reporting

- A table displaying all the actions closed since the last reporting

Everything works fine, and to perform the last two tables, I added a date slicer whose "Field" is configured to "Opening date" or "Closed date"

It's not optimal though, as the "Field" of the slicer comes from the excel file, I can only filter with dates that exist in my file.

I created another dataset with only dates and imported it to my PowerBi desktop dataset.

Would it be possible to use one dataset to populate the dates in my slicer (all the dates from 01/01/2016 to 31/12/2030 for example), but to map this filter to another column of my other dataset?

This way, I could select any date in my filter, and when it doesn't exist, no data would be displayed at all.

If it's not clear, do not hesitate to ask me to clarify.

Thanks.

[Abduvali · Accepted solution]

Hi @Rovand,

All you need to do is to create date table in Power BI or use the one exported from excel. Just create a relationship between 2 date fields, see link below to my library where you can download the solution.

Follow the link: Network-days

Table relationshipDate TableSelecting none existing date

Hope this will help.

Regards

Abduvali

[rodbeck]

That worked, the only thing I changed was the relationship that was many to one as multiple actions can have the same start or end date.

Thanks a lot !