Forum Discussion

RajeevMychael1's avatar
RajeevMychael1
Regular Visitor
9 months ago
Solved

Month/Year column

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!

  • danextian's avatar
    danextian
    9 months ago

    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.

     

7 Replies

  • danextian's avatar
    danextian
    Super User
    9 months ago

    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.

     

  • DataVitalizer's avatar
    DataVitalizer
    Super User
    9 months ago

    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's avatar
      RajeevMychael1
      Regular Visitor
      9 months ago

      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's avatar
        Ashish_Mathur
        Super User
        9 months ago

        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.

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    9 months ago

    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.
  • RajeevMychael1's avatar
    RajeevMychael1
    Regular Visitor
    8 months ago

    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.
     

     

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    8 months ago

    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!