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 | 2021 JAN | Forecast | 2021 | 2/1/2020 |
| 2 | 2021 FEB | Forecast | 2021 | 3/1/2021 |
| 3 | 2021 MAR | Forecast | 2021 | 4/1/2022 |
| 3 | 2021 APR | Forecast | 2021 | 5/1/2023 |
| 4 | 2021 MAY | Forecast | 2021 | 6/1/2024 |
| Period | |
| period_id | period_code |
| 1 | MTD |
| 2 | QTD |
| 3 | YTD |
| 4 | FY |
I would like to create 2 measures start_of_period and end_of period that produce the output below if say 2021 MAY was selected.
| Output | ||||||||
| scenario_id | scenario_code | scenario_type | fiscal_year | forecast_start_date | period_code | calendar_month | start_of_period | end_of_period |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | MTD | May-21 | 5/1/2021 | 5/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | QTD | Apr-21 | 4/1/2021 | 4/30/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | QTD | May-21 | 5/1/2021 | 5/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | YTD | Jan-21 | 1/1/2021 | 1/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | YTD | Feb-21 | 2/1/2021 | 2/2/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | YTD | Mar-21 | 3/1/2021 | 3/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | YTD | Apr-21 | 4/1/2021 | 4/30/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | YTD | May-21 | 5/1/2021 | 5/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Jan-21 | 1/1/2021 | 1/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Feb-21 | 2/1/2021 | 2/28/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Mar-21 | 3/1/2021 | 3/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Apr-21 | 4/1/2021 | 4/30/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | May-21 | 5/1/2021 | 5/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Jun-21 | 6/1/2021 | 6/30/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Jul-21 | 7/1/2021 | 7/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Aug-21 | 8/1/2021 | 8/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Sep-21 | 9/1/2021 | 9/30/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Oct-21 | 10/1/2021 | 10/31/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Nov-21 | 11/1/2021 | 11/30/2021 |
| 1 | 2021 MAY | Forecast | 2021 | 6/1/2021 | FY | Dec-21 | 12/1/2021 | 12/31/2021 |
I tried something like this but it ends up producing a start and end date for all periods/months even when MTD is selected. Ideally, when I select MTD and 2021 MAY, it should only return values for the month of May'21 i.e. result should be 1 row as shown above.
When QTD is selected it should return 2 rows as shown above and not 12 rows.
start_of_period =
VAR __ReferenceDate = SELECTEDVALUE(Scenarios[forecast_start_date])
VAR __Dates = FILTER(VALUES(Calendar[Date]), Calendar[Date] <= __ReferenceDate)
RETURN
SWITCH (
SELECTEDVALUE(Period[period_code]),
"MTD", STARTOFMONTH(__Dates), -- First day of the month
"QTD", STARTOFQUARTER(__Dates), -- First day of the quarter
"YTD", STARTOFYEAR(__Dates), -- First day of the year
"FY", STARTOFYEAR(__Dates) -- First day of the year
)
end_of_period =
VAR __ReferenceDate = SELECTEDVALUE(Scenarios[forecast_start_date])
VAR __Dates = FILTER(VALUES(Calendar[Date]), Calendar[Date] <= __ReferenceDate)
RETURN
SWITCH (
SELECTEDVALUE(Period[period_code]),
"MTD", ENDOFMONTH(__Dates), -- Last day of the month
"QTD", ENDOFMONTH(__Dates), -- Last day of the month
"YTD", ENDOFMONTH(__Dates), -- Last day of the month
"FY", ENDOFYEAR(__Dates) -- Last day of the year
)
2 Replies
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
