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.

     

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