Month offset

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

It is for creating a calendar table by DAX.

The dimension-calendar table in the sample shows current month = 2024 April, because the fact table in the sample is showing up to 2014 April. Once the fact table start to contain 2024 May data, the dimension_calendar table will start to change and start to show current month = 2024 May.

dimension_calendar = 
	VAR _currentmonthend = EOMONTH(
		MAX(fact_sales[date]),
		0
	)
	VAR _startdate = DATE(YEAR(MIN(fact_sales[date])), 1, 1)
	VAR _enddate = DATE(YEAR(MAX(fact_sales[date])), 12, 31)
	VAR _t = ADDCOLUMNS(
		CALENDAR(
			_startdate,
			_enddate
		),
		"year_month", FORMAT(
			[Date],
			"yyyy-mmm"
		),
		"year_month_sort", EOMONTH(
			[Date],
			0
		)
	)
	VAR _currentmonthflag = ADDCOLUMNS(
		_t,
		"current_month_flag", IF(
			[year_month_sort] = _currentmonthend,
			1,
			0
		)
	)
	VAR _monthoffset = ADDCOLUMNS(
		_currentmonthflag,
		"monthindex", RANKX(
			SUMMARIZE(
				_currentmonthflag,
				[year_month_sort]
			),
			[year_month_sort],
			,
			ASC
		)
	)
	VAR _currentmonthoffset = ADDCOLUMNS(
		_monthoffset,
		"currentmonthoffset", MAXX(
			FILTER(
				_monthoffset,
				[year_month_sort] = _currentmonthend
			),
			[monthindex]
		)
	)
	VAR _result = ADDCOLUMNS(
		_currentmonthoffset,
		"offset_from_current_month", [monthindex] - [currentmonthoffset]
	)

	RETURN
		SUMMARIZE(
			_result,
			[Date],
			[year_month],
			[year_month_sort],
			[current_month_flag],
			[offset_from_current_month]
		)