Forum Discussion
Super UserDate Slicer Returns Blank Results When Using Dim_Date to Fact Monthly Costs
Hello everyone,
I’m working with a model where Dim_Date contains a daily date column (e.g., 01-03-2025).
My fact table (Monthly_Costs) is connected to Dim_Date using a 1-to-many relationship (Dim → Fact).
In the Monthly_Costs table, each month is represented by a single date — for example, if I have data for October, all rows for that month use the date 01-10-2025. The fact table is therefore at a monthly grain, not daily.
Everything works correctly when I use the Month field in a slicer.
However, if the user selects a specific Date in the Date slicer (for example, 31-03-2025), the visuals show as blank:
This makes sense because the slicer is filtering on daily dates, while the fact table only contains the first date of each month — but what is the recommended way to handle this scenario?
Should the model be structured differently, or is there a better approach that allows both Month and Date slicers to be used without resulting in empty visuals?
Any guidance or best practices would be appreciated!
Best,
Jacek
Updated: Download example PBIX file
Add a column to your date table that is the first of the month for each date e.g.
Then use that First Date column in the date slicer. It will work fine with your month slicer, which is presumably getting the month from the same date table.
Phil
18 Replies
- PhilipTreacy
Updated: Download example PBIX file
Add a column to your date table that is the first of the month for each date e.g.
Then use that First Date column in the date slicer. It will work fine with your month slicer, which is presumably getting the month from the same date table.
Phil
- jaryszek
Thank you. Interesting idea but how this could work if user wants to filter only for exaple 02.01.2025?
Is still not working...
Best,
Jacek- PhilipTreacy
My understanding was that all dates in a month were the first of that month?
In your initial post you said "if I have data for October, all rows for that month use the date 01-10-2025"
If that is the case why would you have or want a slicer that allows someone to select any other date than the 1st of a month?
And if they could select another date e.g. 2 Oct 2025, what data do you want to be displayed? There is no actual data for the 2nd Oct, so do you show the data for the entire month?
If so, then why bother having any other date in the slicer than the 1st of the month? In fact why have a slicer allowing dates at all? Just use the month slicer.
Please supply your data so that I may understand more clearly.
Phil
- Zanqueta
Hi jaryszek
This is a modelling issue caused by grain mismatch between your date dimension (daily) and your fact table (monthly). When you use a slicer with a specific day (e.g., 31-03-2025), the filter context does not match any rows in the fact table because those rows only contain the first day of each month.Recommended Approaches
Option 1: Use a Month-Level Relationship
- Create a MonthKey in both tables:
- In Dim_Date: YearMonth = FORMAT([Date], "YYYYMM")
- In Monthly_Costs: YearMonth = FORMAT([Date], "YYYYMM")
- Change the relationship to use YearMonth instead of the daily date.
- Keep the daily date column in Dim_Date for slicers, but the relationship will now work at the month level.
- Then, in your measures, use Dim_Date[Date] for visuals and slicers, but the filter will propagate correctly because the relationship is based on YearMonth.
Best Practice
If your fact table is truly monthly, the relationship should reflect that grain. Using daily keys for monthly data often leads to blank results when filtering by specific dates.Official Reference:If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
- jaryszek
Thank you very much for the explanation!
Could you please provide a small example of how this should be implemented?
I tried creating a YearMonth column in both Dim_Date and the fact table, but since Dim_Date is at a daily grain, the YearMonth values are duplicated. Because of that, I can't create a 1-to-many relationship between the tables.I’m not sure what the recommended modelling approach should look like in this case, so an example would be very helpful.
I will include my sample dataset in the post as well:
https://drive.google.com/file/d/1d2vZnyVz3LqKjqNtCpAsAVqiZVaybLdY/view?usp=sharing
Best,
Jacek- Zanqueta
Hello jaryszek,
I agree whit raisurrahman.
Just to clarify: if your fact table is monthly, does that mean you have another dimension in your model when you need daily data?I don’t understand why this date filter is still enabled.Wouldn’t it be simpler to remove the daily filter?If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
- Create a MonthKey in both tables:
- Chewdata
Responsive Resident
Hey!
First can you check if your dimtable date column is of the date or datetime type?
Sometimes if the dim datetable has datetype as it's main date column (so 04-12-2025 00:00:00 instead of 04-12-2025) It won't match correctly.- jaryszek
There is the match. But designing it like that just can not work...because when user will select 2025-02-02 Month does not have a value like that becuase key is only 2025-02-01 (so first day in a month).
I could add per each month record specific Date buty it would increase table by 30 times...(per each day).
Best,
Jacek
raisurrahmanHelper II
I noticed that your Monthly Cost fact table is at a monthly grain. Therefore, you need to align your Date dimension to match this granularity. For example, if you build a Date dimension for the year 2025, it should contain exactly 12 rows, with each row representing the first date of the month.
- jaryszek
Thanks! That would work for a very simple case, but I need daily slicing too — so a 12-row Date table isn’t sufficient.
- jaryszek
What if I will crete Dim_Month and relate it 1 to many with Dim_Date?
It could work? Anybody tested thiskind of approach?
Best,
Jacek
