Forum Discussion
Understanding Date Table and How It Filters
Hi Txtcher ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
Yes, the best and most efficient practice is to add a new column to your history table that contains only the date (without the time) and use that for your relationship to the calendar table.
This allows you to:
-
Maintain the original date/time values for analysis that needs time-level detail.
-
Use your calendar table (which only includes dates) effectively in slicers and visuals without needing a massive minute-by-minute calendar.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
-
Hello,
I would recommend reading up on the star schema. A helpful article can be found here:
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
Could you maybe share some screenshots of the relationship, and what the result should look like? It would be helpful to also see the date table and make sure that every date that corresponds to a fiscal year all have the fiscal year listed (like this:)
| Date | FY |
| 6/29/2024 | FY 2024 |
| 6/30/2024 | FY 2024 |
| 7/1/2025 | FY 2025 |
| 7/2/2025 | FY 2025 |
Thank you for the article link.
Here are screen shots (this is just some sample data for testing and learning purposes).
Calendar table - yes it has at Fiscal Year for each date in the calendar:
The history table is simple (huge, but simple).
Relationships:
This seems like it should be pretty basic and simple, but ...I am clearly lost.
Hi Txtcher
You calendar table should be able to filter your HX table based on the images provided. Please check if:
- calendar covers all dates in HX
- relationship is between the same data types HX[CreatedDate] could be a datetime but appears as date only on data/table view. Note: Type any (ABC123) is also considered a text.
Hi Txtcher, as some others have commented, it seems like you have everything set up correctly. I would recommend checking if you are linking the dates with the same format. I.e. make sure both columns are in either date, or date/time format. Sometimes this can be a little bit tricky and cause problems if they are not the same.
A good way think about it is if one column has a date as (1/1/2025, 12:00:00am) and the other as (1/1/2025), they may seem the same but the relationship is not valid because the first column has a time element that it can't match to in the second column. Hope that makes sense and is what is causing your Power BI to not return the expected result!
