Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

Month offset

Hi, I need to create a new column which is based on date and current month. My report is always 1 month behind so April will be my current month. 

 

What I want is for my new column to show current month (in my case 1 month behind) as 0 and for March-1, Feb -2, Jan -3, Dec -4 etc.

 

But I want this change so when we get May's data that becomes 0, April -1, March -2, Feb -3, Jan -4 and Dec -5. So it changes based on current month. Is this possible please?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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]
		)

     

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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]
		)