@Max,
Wow – this one ended up being much more difficult than it initially looked. Huge shout out to @Melissa, who gets most of the credit for this one after I took an ill-conceived virtual table approach to the solution initially and she got me back on track. Here’s the measure that does most of the heavy lifting:
DAU Penetration Forcast4 =
VAR LastCompleteMonth =
MONTH( TODAY() ) - 1
VAR CarryOverAmt =
CALCULATE (
CALCULATE (
[DAU Penetration Rate (actuals)],
Scenario[Scenario] = "Actuals" ),
FILTER (
ALL ( Dates ),
Dates[MonthOffset] =
CALCULATE (
[Max MonthOffset],
Scenario[Scenario] = "Actuals"
)
)
)
VAR Result =
IF(
AND(
SELECTEDVALUE( Dates[MonthOfYear] ) > LastCompleteMonth,
SELECTEDVALUE( Scenario[Scenario] ) = "Forecast"
),
CarryOverAmt,
BLANK()
)
RETURN
Result
This measure then controls which rows are displayed subject to the visual filter:
RowFilter =
VAR LastCompletedMonth =
MONTH( TODAY() ) - 1
VAR Cond1 =
IF(
AND(
[Sel Scenario] = "Actuals",
SELECTEDVALUE( Dates[MonthOfYear] ) <= LastCompletedMonth
),
1,
0
)
VAR Cond2 =
IF(
AND(
[Sel Scenario] = "Forecast",
SELECTEDVALUE( Dates[MonthOfYear] ) > LastCompletedMonth
),
1,
0
)
VAR Cond3 =
IF(
AND(
[DAU Penetration Rate (actuals)] = BLANK(),
[DAU Penetration Forcast4] = BLANK()
),
0,
1
)
VAR Result =
IF(
OR(
Cond1 = 1,
Cond2 = 1
),
1,
0
)
RETURN
IF(
Result + Cond3 = 2,
1,
0
)
Important note: the solution relies on the use of date table offsets. Thus we had to change your date table over to the Extended Date Table to gain the use of the offset functionality. If you need more information about the Extended Date Table and/or use of offsets, please refer to the video series that @Melissa and I did on this topic, which can be found here.
I hope this is helpful. Full solution file attached below.
- Brian