Date Function Problem

[Diportee97]

•  

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!

[ajaybabuinturi · Accepted solution]

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
  

[Diportee97]

My pbix file

https://drive.google.com/file/d/1cAV_OsnCk-IH8Le0TMe6PnQSXQ1JGIdg/view?usp=drivesdk 

My dashboard link

https://app.powerbi.com/view?r=eyJrIjoiMWVhNzk0Y2ItZWY0Ni00NDY1LTlhOTktMjVjZTI5MTYxMmZhIiwidCI6IjY4Z...

[Anonymous · Accepted solution]

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 Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ajaybabuinturi · Accepted solution]

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
  

[ajaybabuinturi]

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.

[Diportee97]

• 

 

 

 These are my calendar table and fact table and all data types are in date type,plus the table were all connected appropriately 

[ajaybabuinturi]

Could you please share the sample bi report. if possible.

[Diportee97]

https://drive.google.com/file/d/1cAV_OsnCk-IH8Le0TMe6PnQSXQ1JGIdg/view?usp=drivesdk 

Thats the sample of my pbix file

[Murtaza_Ghafoor]

Please  check  the relationship between  dates.

[Diportee97]

    These are my calendar table and fact table and all data types are in date type,plus the table were all connected appropriately