Date table slicer not working

[Solle]

Hi all, 

I have been browsing the web and tried all possible solutions that I could find and been going throug my data several times, however everything seems fine. 

I have generated a date table from https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390 and loaded my data, where I have transformed the As Of Date as "Date" such that my date table and my data has the same format (please see below)

Own data - Power Query

Date Table - Power Query

Then after I marked my Date Table as a "Date Table" by using column "Date" as seen below

Then I tried to generate a simple matrix which is connected to a slicer.

However the slicer contains all dates that I have in my date table, however there is only data from 15-04-2021 until 06-05-2022. 

Does anyone know why it won't work - I have used a date table before where I had no issues on another dataset. 

Best Regards,
Solle

[mussaenda]

Hi @Solle ,

You have the correct relationship.

Are you sure you have data before 15/04/2021?

Try adjusting you slicer after 15/04/2021.

If you table filters, it means you have no data before the 15/04/2021.

Hope this helps.

[Solle]

Hi @mussaenda
Thank you for your reply - My first observation is on the 15/04/2021. 

[Shishir22]

Hello @Solle ,

The date slicer is working as expected.

There is no data for dates before 15th April and hence there is no data coming in visual.

I have sorted data as per date and we have data from 15th April 2021-

If your want to see blanks for all the dates selected in slicer-

Please enable "Show Items for no data" for Date feild

Please mark it as solution if it solves your issue. Kudos are also appreciated.

[Solle]

Hi @Shishir22 

The slicer shows all dates that are available in my calendar table, why is that?

Not sure I have the problem solved, unless I misunderstand how the slicer should be, what I am expecting from the slicer is to only show dates where data is present and not the full range of my date table? 

Can you please share the file that you have made or what you did if you have the result that I seek?

Appreciate your help alot!

Best Regards,
Solle

[Shishir22]

Hello @Solle ,

To just see the dates having data. Please uncheck "Show Items with No data"..

Please mark it as solution if it resolves your issue. Kudos are also appreciated.

[Solle]

Hi, 

There is no way for the slicer to only span from 15-04-2021 until latest observation? 
Such that it is not possible to select 20-12-2025 i.e?

Thats where my struggle is.

Thank you 🙂 

Best Regards,
Solle

[mussaenda]

Of course you can filter you date slicer.

Is your market value on the matrix a measure?

if yes, drag it to visual filter of the slicer and filter it to value is not 0.

if not, create a measure of the market value and drag it to the filter value of the date slicer.

Hope this helps.

[Shishir22]

Hello @Solle ,

To select till latest observation you can use after date range slicer instead of between.

Please mark it as solution if it resolves your issue. Kudos are also appreciated.

[Solle]

Hi @Shishir22

I think that I might not have expressed me properly, but what I would like is to only be able to select dates where there is actual data - In the sample file it starts at 01-04-2021 however the data is at the earliest present at 15-04-2021 and in addition to this for some other slicers I might want a dropdown menu on years, where it should only show 2021 and 2022 as thats where the data is present. 

Please let me know if you know how to fix this issue and if any further information is required from me. 

Thank you yet again very much!!

Best Regards,
Solle

[Anonymous · Accepted solution]

In that case, create your very own custome calendar table.

The question is, do you need dates or hours ?

If days only, create in Power BI (not power query) a new table, and put the following formula:

Calendar Table = CALENDAR(MIN('Table1'[yourDate]),MAX('Table1'[yourDate]))

Replace of course 'Table1'[yourDate] by the column of you main data table where there is the dates. This will create a table with single column containing dates cfrom the beginning to the last record. You can then use it as a Date:Calebndar table but don't mark it as a Date table.

[Anonymous]

What's the relationship between your Date table and your own table ? 

You need to atatch your own data to the date table as * towards 1.

[Solle]

Hi @Anonymous 
How do you do that?

[Anonymous]

Here : 

Then this 

Then you make the column "day" of your date table attached to the columns "day" of you own data.

As you have multiple times the same day in your own data, multiple lines correspond to one and only day in your date table. Even if you ahve 3 lines "28/04/2022", you want to have in your date table only one line "28/04/2022" but when you select it you have your multiple evnts sotred there

[Solle]

Hi @Anonymous 
Thank you for your response, however I am only able to generate this

When I try the opposite direction it points the same direction...

Best Regards,
Solle

[Anonymous]

I can't see what column of the big red table you selected to tie to the date of the date table. From what I can imagine, it seems that the main table has multiple lines with the same day dd/mm/yyyy. Is it correct ? Coulad you at least show the table name so we understand each other better ?

[Solle]

Hi @Anonymous 
Sorry please see the attached. Yes the main table "Reporting Reporting" has multiple lines with the same dates correct. 

Let me know if anything else is required. 

Best Regards,
Solle

[Solle]

Hi @Shishir22 

I am extracting the data from a SQL table, is it possible for me to hide that part somehow?

Best Regards

[Shishir22]

Hello @Solle ,

Do you have values (Market value) correspond to dates pior then 15-04-21?

Request you to please share .PBIX post hiding sensitive data.

[Solle]

Hi @Shishir22 
I managed to extraxt some sample data, please see the link for the .pbix file. 

https://we.tl/t-aBZhbcB877

Appreciate your help a lot!

Best Regards,
Solle