Partially syndicated - Outbound Using Previous Month Value for Future

Power BI
21

@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
5 Likes
22

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!