How to create a date slicer for 2 data fields

[sacheu]

Hi,

In my PowerBI page, I have 2 widgets with 2 different tables. Both tables have a column 'date'.

I want to create 1 'data slicer' so that when I change the range of the date, both widgets will get update accordingly.

I create a Date Slicer, and add both 'Date' fields in each table to the 'Add data fields here' under Filter'.

But when I test it, only 1 widget get update with the selected range of the date. Can you please tell me how to fix the issue?

Thank you.

[Anonymous]

HI @sacheu,

You can try to extract the date values from two tables to create a calendar, that you can use this as the bridge to link to tables.
After these steps, you can use bridge calendar to create a slicer and it will apply filter effects to two table records.

Regards,

Xiaoxin Sheng

[sacheu]

Thanks. How can I create a calendar from 2 tables?

[Anonymous]

Hi @sacheu,

You can try this calculate table formula if it works on your side:

New Table =
VAR _list =
    UNION ( ALL ( Table1[Date] ), ALL ( Table2[Date] ) )
RETURN
    CALENDAR ( MINX ( _list, [Date] ), MAXX ( _list, [Date] ) )

Regards,

Xiaoxin Sheng

[sacheu]

I am using an Excel spreadsheet online as the data source of my PowerBI report. In this setting, I don't see a way which let me enter the above formula  in the report.

[Anonymous]

HI @sacheu,

These are dax expression which works on power bi desktop, if you directly upload your excel sheet to the power side as the source to create the report, you can't use these expressions. 

Power bi service does not support these edit features of calculated fields, please design your report on power bi desktop side and you will find the formula bar.

Comparing Power BI Desktop and the Power BI service 

Regards,

Xiaoxin Sheng

[sacheu]

Thanks. But when I use 'Power BI desktop', it does not allow me to use and Online Excel spreadsheet as the data source of my report. The excel spreadsheet needs to be Online since it is periodically getting updates. I only let me import a local Excel spreadsheet.

[Anonymous]

HI @sacheu,

>> Translate the `Date’ column of the Table into Pacific Time zone

You can add a custom column on the query editor side to convert DateTime to UTC format and use 'DateTime.AddZone' function to calculate with the timezone offset, it will cover the date value to specific timezone values.
Notice: switch timezone functions not change the raw DateTime values.

>> Strip out the Time of the ‘Date’ column so that it just has the date (e.g. 1/19/2021 )

You can change the date field type to date, then it will not display the time parts of your values.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @sacheu,

Please take a look at the following link to know how to find out the root file link of files stored on a cloud server, then you can use power bi desktop to get data from the online file directly:

Use OneDrive for Business links in Power BI Desktop 
Regards,

Xiaoxin Sheng