Forum Discussion
Date Function Problem
Hello everyone, I'm facing a major issue with the Date function in Power BI. The function correctly matches the original data's date, and both are properly linked. However, the problem occurs on my dashboard—whenever I select a year in the slicer, my card visuals return empty values. I’d really appreciate any help or possible solutions. Thanks in advance!
Hi Diportee97
The main issue is calendar table, date column(Date/time data type) having same time for all dates(shows 12:00 AM for all dates). It means whenever you filter the data it will look for same date and time. That's why you can't see the data.
Hence, I changed date column as 'Date' data type only ratherthan Date/time data type in the calendar table and created a new date column with 'Date' data type only in Hosptial_ER table.
Now you can able to see the data. Here I am attching a smaple .pbix file url
https://drive.google.com/file/d/1lTR79-mNM_p46hzSqp5kptNrFA2IrvoI/view?usp=sharing
Hi Diportee97
Thanks for the reply from Murtaza_Ghafoor and ajaybabuinturi .
My suggestion is to create a date column in both the Calendar and Hospital ER tables, and then create a relationship between the two tables based on this column.
Date without time = FORMAT('Hospital ER'[Date], "yyyy-mm-dd")Date without time = FORMAT('Calendar'[Date], "yyyy-mm-dd")Output:
This is done because the time in the calendar table is 12:00am, through my test, if the column with time is used as a slicer, only the data at 12:00am in the Hospital ER table can be filtered.
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Please check the relationship between dates.
These are my calendar table and fact table and all data types are in date type,plus the table were all connected appropriately
Hi Diportee97 ,
I belive the relationship between date table and data table(fact) is not properly set-up or fact table data may be in other data type.
I would recommend check the date column datatypes and it should be in 'Date' datatype.These are my calendar table and fact table and all data types are in date type,plus the table were all connected appropriately
Could you please share the sample bi report. if possible.
Hi Diportee97
The main issue is calendar table, date column(Date/time data type) having same time for all dates(shows 12:00 AM for all dates). It means whenever you filter the data it will look for same date and time. That's why you can't see the data.
Hence, I changed date column as 'Date' data type only ratherthan Date/time data type in the calendar table and created a new date column with 'Date' data type only in Hosptial_ER table.
Now you can able to see the data. Here I am attching a smaple .pbix file url
https://drive.google.com/file/d/1lTR79-mNM_p46hzSqp5kptNrFA2IrvoI/view?usp=sharing
Hi Diportee97
Thanks for the reply from Murtaza_Ghafoor and ajaybabuinturi .
My suggestion is to create a date column in both the Calendar and Hospital ER tables, and then create a relationship between the two tables based on this column.
Date without time = FORMAT('Hospital ER'[Date], "yyyy-mm-dd")Date without time = FORMAT('Calendar'[Date], "yyyy-mm-dd")Output:
This is done because the time in the calendar table is 12:00am, through my test, if the column with time is used as a slicer, only the data at 12:00am in the Hospital ER table can be filtered.
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
