Month/Year column

[RajeevMychael1]

I'm working with a dataset that only has Posting Month and Posting Year (no specific dates). I created a date column by setting all dates to the 1st of each month (e.g., 01/01/2024, 01/02/2024) to use with a between date slicer.

Problem: When users select a date range like 15/01/2024 to 15/02/2024, only February shows because 01/01/2024 falls outside this range.

I've tried:

• Setting dates to the end of month (EOMONTH) - but then different date selections show different months
• Creating measures to check date range overlap - doesn't work with slicer filtering
• Using a separate Date/MonthYear table with relationships

What I need: Users should see ALL months that fall within the selected date range, regardless of which specific day they choose within those months.

Is there a way to make a between date slicer work properly with month-level data, or should I abandon the between slicer approach entirely?

Any suggestions appreciated!

[v-sgandrathi]

Hi @RajeevMychael1,

Thank you for sharing your final solution and the DAX used for the Calendar table. This will be helpful for others facing the same month-level slicer issue. Your method of creating a complete calendar with MonthStart and MonthEnd, and syncing the two sheets, matches the suggestions above and is a reliable data-modeling pattern in Power BI.

Glad to hear everything is working as expected now, and thanks again for updating the thread with the final solution!

[RajeevMychael1]

Thanks for all your responses and help. Did manage to fix this by creating a table with the below formula 

Calendar =

VAR MinYear = 2019

VAR MaxYear = 2030

VAR MinDate = DATE(MinYear, 1, 1)

VAR MaxDate = DATE(MaxYear, 12, 31)

RETURN

ADDCOLUMNS(

    CALENDAR(MinDate, MaxDate),

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "MonthYear", FORMAT([Date], "MMM yyyy"),

    "MonthStart", EOMONTH([Date], -1) + 1,

    "MonthEnd", EOMONTH([Date], 0)

)

As I had 2 sheets sync and filters worked as well. Once again appreciate your efforts and help.

[danextian · Accepted solution]

Hi @RajeevMychael1 

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.

[Ahmed-Elfeel]

Hi @RajeevMychael1,

You can create a dedicated date table that defines the actual start and end dates for each month:

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),
    "Month Start", EOMONTH([Date], -1) + 1,
    "Month End", EOMONTH([Date], 0),
    "Month Year", FORMAT([Date], "MMM YYYY")
)

Then create relationships and use this logic in your measures:

Sales Amount Filtered = 
CALCULATE(
    [Sales Amount],
    FILTER(
        'DateTable',
        'DateTable'[Month Start] <= MAX('SlicerDateTable'[Date]) &&
        'DateTable'[Month End] >= MIN('SlicerDateTable'[Date])
    )
)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[DataVitalizer]

Hi @RajeevMychael1 

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 💡

🟩 Follow me on LinkedIn

[RajeevMychael1]

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.

[Ashish_Mathur]

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.