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!

23

5 Years Later I had the same issue and couldn’t find anything proper on the web. If I may reword this fantastic solution and dumb it down a little for any other person looking for an answer that fits their data set without looking at specific columns.

The main measure CarryOverAmt is separated into 2 parts
Calculation of values to use for the Forecast

Calculate (
Values,
Filter for values (Actuals here, in my dataset I used past dates (Dates < Today() )
)

Then the 2nd part is the filter for the first resulted table

Filter (
Keep only certain dates (the formula is not needed if for example; like me you just use past data with [Date] < Today ()
)

Use both of these in a Calculate and you get your result