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 us...
  • 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.

     

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.