cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kfausch Helper I
Helper I

Date Filter Issue

I have a few tables in Power BI that have date columns, I want to use 1 filter to control all of them in one report so I created a date table and created the relationships. However now when I filter by date using the new date table, the reports show up blank and I'm not sure why. I have done this a few times and never had an issue.

 

Anyone have any idea what the issue is here?

 

The first screen shot I'm using the date from the main table (Av_shift) to filter, which works fine. The second I'm trying to filter using the new date table, but the report just goes blank. 

 

I also added some screen shots of the relationship and data showing that the data type is set to Date/Time.

 

Here is the Dax for the Date table:

let CreateDateTable = (StartDate as date, EndDate as date, FiscalYearStartDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each Number.ToText([Year]) & " " & "Q" & Number.ToText([QuarterOfYear])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])+1),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),

InsertFiscalMonth = Table.AddColumn(InsertWeekEnding, "FiscalMonth",
each ( if Date.Month([Date]) >= Date.Month(FiscalYearStartDate) then Date.Month([Date]) - Date.Month(FiscalYearStartDate) + 1
else 12 - Date.Month(FiscalYearStartDate) + Date.Month([Date]) + 1 ), type number),

InsertFiscalQuarter = Table.AddColumn(InsertFiscalMonth, "FiscalQuarter", each Number.ToText([FiscalYear]) & " FQ" & Number.ToText([FiscalCalendarQuarter])),

InsertFiscalCalendarQuarter = Table.AddColumn(InsertFiscalQuarter, "FiscalCalendarQuarter",
each ( if Date.QuarterOfYear([Date]) >= Date.QuarterOfYear(FiscalYearStartDate) then Date.QuarterOfYear([Date]) - Date.QuarterOfYear(FiscalYearStartDate) + 1
else 4 - Date.QuarterOfYear(FiscalYearStartDate) + Date.QuarterOfYear([Date]) + 1)),

InsertFiscalYear = Table.AddColumn(InsertFiscalCalendarQuarter, "FiscalYear", each (if Date.Month([Date]) < Date.Month(FiscalYearStartDate) then Date.Year([Date]) else Date.Year([Date])+1), type number)
in
InsertFiscalYear,
#"Invoked FunctionCreateDateTable" = CreateDateTable(#date(2018, 12, 8), #date(2020, 12, 31), #date(2018, 1, 1), null)
in
#"Invoked FunctionCreateDateTable"

 

 

 

PowerBIDate.PNG

PowerBIDate2.PNGPowerBIDate3.PNGPowerBIDate4.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
blopez11 Solution Sage
Solution Sage

Re: Date Filter Issue

I have found that the date column in the date table and in the table it has a relationship with, both need to be of type date.   In the query editor, try changing the type for startTime to date.  If you need startTime for be of datetime for other reasons, duplicate the column, set its type to date, and use that in the relationship with the date table

 

Good luck

View solution in original post

2 REPLIES 2
blopez11 Solution Sage
Solution Sage

Re: Date Filter Issue

I have found that the date column in the date table and in the table it has a relationship with, both need to be of type date.   In the query editor, try changing the type for startTime to date.  If you need startTime for be of datetime for other reasons, duplicate the column, set its type to date, and use that in the relationship with the date table

 

Good luck

View solution in original post

Kfausch Helper I
Helper I

Re: Date Filter Issue

Awesome that worked! I added this and applied then it connected.

 

= Table.TransformColumnTypes(Av_shift_View,{{"startTime", type date}})

 

Thanks for your help!!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors