Partially syndicated - Outbound Using Previous Month Value for Future

Thank you, Harsh. Update: this is what I’m seeing now:

I’m now seeing the future month value but it’s still not carrying the previous month values over.

Here is the “New Measure” that should theoretically be doing what I want it to…
New Measure = IF ( MAX ( ‘Dates’[Date] ) < Today(),[Forecast Rate(actuals)],[previous month])

Hello @Max,

I think you’re receiving these results because of date context.

You can try these measure given below -

 `New Measure = 
  IF ( MAX ( FactTable[Date] ) <= Today(),
         [Forecast Rate(actuals)],
               [previous month])`

Thanks & Warm Regards,
Harsh

Which table is the FactTable? All my tables are connected to a Date table

Hello @Max,

Instead of Date Table use the Table in which you’ve data. For e.g. “Sales Table”.

I’m attaching the PBIX file of my working for the reference.Partially Syndicated.pbix (101.6 KB)

Thanks & Warm Regards,
Harsh

Still no luck.

Hello @Max,

If possible can you please provide your PBIX file.

If it’s confidential you can mask your data. I’m providing a link of a video about how you can mask your sensitive data which was created by @BrianJ and then create a sample file of it so that we can have a look at your problem.

Thanks & Warm Regards,
Harsh

Uploaded.

Just want to reiterate that the future month % should be the same as the previous months. So in this it should be 10.24% from July - December

Hello @Max,

Firstly I would like to apologize because the file which you’d sent to me didn’t opened up in my machine due to version that I’m using so I was not able to look at the file.

Unable to Open Document

But I would like to give my last shot in order to get that answer before I rest my case. You can try out the below formula -

Sales Last Purchase = 
VAR PriorDate = CALCULATE( 
                   MAX( FactTable[FactTable Date] ),
                       FILTER( 
            		       ALL( FactTable[FactTable Date] ), 
            				   FactTable[FactTabl Date] < MAX( FactTable[FactTable Date] ) ) )

RETURN
    IF( MAX( Sales[Date] ) <= TODAY() , 
    	[Forecast Rate(actuals)] , 
    		CALCULATE( [previous month],
    			FILTER( 
                   ALL( FactTable[FactTable Date] ),
    				  FactTable[FactTable Date] = PriorDate ) ) ) 

OR

Sales Last Purchase = 
    VAR PriorDate = CALCULATE( 
                       MAX( Dates[Date] ),
                           FILTER( 
                		       ALL( Dates[Date] ), 
                				   Dates[Date] < MAX( Dates[Date] ) ) )

RETURN
  IF( MAX( Dates[Date] ) <= TODAY() , 
     [Forecast Rate(actuals)] , 
         CALCULATE( [previous month],
        	 FILTER( 
                ALL( Dates[Date] ),
                    Dates[Date] = PriorDate ) ) ) 

Just note the FactTable will be table in which your Actuals and Forecasted data is stored combined.

Hoping this is useful. I’m also attaching the PBIX file of my working.

The concept which I’ve applied in this formula is as per the given video.

Thanks & Warm Regards,
Harsh

Partially Syndicated.pbix (104.7 KB)

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

Thanks, Harsh–but I think there’s a miscommunication. What I essentially want is this:

Product Name Month Scenario Sales Last Purchase2 previous month
Product 1 January Actuals 0.1036
Product 1 February Actuals 0.1022
Product 1 March Actuals 0.1014
Product 1 April Actuals 0.1025
Product 1 May Actuals 0.0995
Product 1 June Actuals 0.0267
Product 1 July FY Plan 2.67%
Product 1 August FY Plan 2.67%
Product 1 September FY Plan 2.67%
Product 1 October FY Plan 2.67%
Product 1 November FY Plan 2.67%
Product 1 December FY Plan 2.67%
Product 2 January Actuals 0.0819
Product 2 February Actuals 0.0797
Product 2 March Actuals 0.0807
Product 2 April Actuals 0.082
Product 2 May Actuals 0.0716
Product 2 June Actuals 0.0033
Product 2 July FY Plan 0.33%
Product 2 August FY Plan 0.33%
Product 2 September FY Plan 0.33%
Product 2 October FY Plan 0.33%
Product 2 November FY Plan 0.33%
Product 2 December FY Plan 0.33%

The **Sales Last Purchase 2 column for future months won’t have any actuals so it will need to carry the previous values down to the rest of the year.

Currently this is what it shows:

Product Name Month Scenario Sales Last Purchase2 previous month
Product 1 January Actuals 0.1036
Product 1 February Actuals 0.1022
Product 1 March Actuals 0.1014
Product 1 April Actuals 0.1025
Product 1 May Actuals 0.0995
Product 1 June Actuals 0.0267
Product 1 July FY Plan 9.88%
Product 1 August FY Plan 9.81%
Product 1 September FY Plan 9.67%
Product 1 October FY Plan 9.35%
Product 1 November FY Plan 9.21%
Product 1 December FY Plan 9.13%
Product 2 January Actuals 0.0819
Product 2 February Actuals 0.0797
Product 2 March Actuals 0.0807
Product 2 April Actuals 0.082
Product 2 May Actuals 0.0716
Product 2 June Actuals 0.0033
Product 2 July FY Plan 9.57%
Product 2 August FY Plan 9.55%
Product 2 September FY Plan 9.54%
Product 2 October FY Plan 9.55%
Product 2 November FY Plan 9.57%
Product 2 December FY Plan 9.57%

Hello @Max,

My sincere apologies because since I’m not able to open up a file in my machine it’s really getting difficult for me to evaluate the problem.

But don’t worry I’ve messaged to one of our experts @BrianJ and he said he’ll surely look into your problem and will come with the solution.

Again my sincere apologies for the inconveniences caused to you and was not able help you out due to my system error.:pensive:

Warm Regards,
Harsh

No worries at all, @Harsh!

@BrianJ – this is what the data currently looks like (disregard the FY Plan numbers, those are fine–focus on the Actual & Forecast numbers):

Measures:

Previous Month = CALCULATE([DAU Penetration Rate (Actuals)], PREVIOUSMONTH(Dates[Date]))

DAU Penetration Rate (Actuals) = CALCULATE(DIVIDE([Total Users],[Combined Devices]))

Total Users = CALCULATE(SUM ( ‘Landing Pages Final’[Value] ), FILTER ( ‘Landing Pages Final’, ‘Landing Pages Final’[Metric] = “avg_daily_unique_users”))

Combined Devices = CALCULATE(SUM(‘Landing Pages Forecast’[Android Devices Base]))

Relationships

Ultimately I would like the most recent percentage to carry into the future months forecast. In this case the percentages from June - December should be the same.

So for example…

Product Name Month Scenario Total Users DAU Penetration Rate (Actuals)
Product 1 January Actuals 1,737,227.25 10.36%
Product 1 February Actuals 1,703,325.75 10.22%
Product 1 March Actuals 1,738,796.63 10.14%
Product 1 April Actuals 1,749,688.13 10.25%
Product 1 May Actuals 1,743,831.38 10.25%
Product 1 June Actuals 1,747,805.50 9.00%
Product 1 July Forecast 1,755,231.63 9.00%
Product 1 August Forecast 1,761,434.38 9.00%
Product 1 September Forecast 1,772,202.50 9.00%
Product 1 October Forecast 1,776,808.25 9.00%
Product 1 November Forecast 1,784,368.50 9.00%
Product 1 December Forecast 1,796,997.50 9.00%
Product 2 January Actuals 2,073,076.00 17.40%
Product 2 February Actuals 1,559,042.00 16.52%
Product 2 March Actuals 1,903,785.00 14.22%
Product 2 April Actuals 2,202,469.00 10.22%
Product 2 May Actuals 1,869,076.00 11.44%
Product 2 June Actuals 1,821,604.00 14.65%
Product 2 July Forecast 2,216,924.00 14.65%
Product 2 August Forecast 1,724,131.00 14.65%
Product 2 September Forecast 2,446,829.00 14.65%
Product 2 October Forecast 2,124,084.00 14.65%
Product 2 November Forecast 1,571,207.00 14.65%
Product 2 December Forecast 1,682,086.00 14.65%

@Max,

Thanks very much for the additional clarification. I worked on this until late last night – think I was close to a solution and then we lost power due to a violent thunderstorm. Will get back to it after work today, and hopefully have a solution to you this evening.

  • Brian

Thanks for the update, @BrianJ!

@Max,

Still working hard on this one, but it’s proving extremely recalcitrant. Have called in reinforcements to get fresh eyes on the problem. Will continue to keep you posted.

  • Brian

Thanks, @BrianJ. Looking forward to what you come up with.

Thank you for enriching our vocabulary bank meanwhile :smiley:

1 Like

@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
4 Likes

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!