Forum Discussion
Time Intelligence based on a specific date as reference
Suppose I have a Scenarios table and a disconnected Period Table as shown below. Scenarios scenario_id scenario_code scenario_type fiscal_year forecast_start_date 1 20...
HI, Make sure your Calendar table is connected appropriately and try this:
start_of_period =
VAR __ReferenceDate = SELECTEDVALUE(Scenarios[forecast_start_date])
VAR __SelectedPeriod = SELECTEDVALUE(Period[period_code])
VAR __StartOfMonth = STARTOFMONTH(__ReferenceDate)
VAR __StartOfQuarter = STARTOFQUARTER(__ReferenceDate)
VAR __StartOfYear = STARTOFYEAR(__ReferenceDate)
RETURN
SWITCH(
__SelectedPeriod,
"MTD", __StartOfMonth, -- Start of the month for MTD
"QTD", __StartOfQuarter, -- Start of the quarter for QTD
"YTD", __StartOfYear, -- Start of the year for YTD
"FY", __StartOfYear -- Start of the fiscal year for FY
)
end_of_period =
VAR __ReferenceDate = SELECTEDVALUE(Scenarios[forecast_start_date])
VAR __SelectedPeriod = SELECTEDVALUE(Period[period_code])
VAR __EndOfMonth = ENDOFMONTH(__ReferenceDate)
VAR __EndOfQuarter = ENDOFQUARTER(__ReferenceDate)
VAR __EndOfYear = ENDOFYEAR(__ReferenceDate)
RETURN
SWITCH(
__SelectedPeriod,
"MTD", __EndOfMonth, -- End of the month for MTD
"QTD", __EndOfQuarter, -- End of the quarter for QTD
"YTD", __EndOfMonth, -- End of the month (could change for YTD but typically aligns with current month)
"FY", __EndOfYear -- End of the fiscal year for FY
)
This gives an error stating that STARTOFMONTH expects a column. I tried adding a new variable __Dates and passed that instead of __ReferenceDate but that is producing a blank.
VAR __Dates = FILTER(VALUES(Calendar[Date]), Calendar[Date] = __ReferenceDate)
Here's a link to a -> sample PBIX file