Forum Discussion
Month/Year column
Use a full calendar table with out skips and a column that returns the start or end of month.
Create a many-to-many relationship between the start date columns with calendar filtering the fact table but use the date column in the slicer
Or establish a virtual relationship between the two tables using a measure
Please see the attached pbix.
The between date slicer in Power BI works at the day level, If you only have Posting Month and Posting Year, and you create artificial dates like 01/01/2024, the slicer will filter by that exact day and that’s why selecting 15/01/2024–15/02/2024 excludes January, the slicer sees 01/01/2024 as outside the range.
The correct approach is to model your data at the month level instead of forcing day values:
- Create a MonthYear key (e.g. 202401, 202402) or a dedicated Month dimension table.
- Relate this to your fact table by Posting Month and Year.
- Use that MonthYear field in a slicer (either as a list or numeric between).
This way, when users select January–February, both months are included regardless of which day they choose.
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡
Hi I tried this method earlier and i think this is the way to go. I tried this method the reason i backed from this is when i m moving the slider its showing some numbers which is not present in the column. Attaching the picture for the reference.
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
