Partially syndicated - Outbound Using Previous Month Value for Future

I’m having difficulty creating a new column that uses the previous month’s Forecast Percentage for future months. I’m using the measure below but it’s just showing blanks for August onwards. Related measures below

``````DAU Measure 3 = CALCULATE([Average Users (actuals)],PREVIOUSMONTH(Dates[Date]))

Average Users (actuals) = CALCULATE(DIVIDE([DAU 1M MA],[Total Devices Base - Actuals]))

DAU 1M MA =
IF ( MAX ( 'Dates'[Date] ) <= TODAY (),AVERAGEX(
DATESINPERIOD( Dates[Date],
LASTDATE( Dates[Date] ),
-1, MONTH ),
[Total Users2] ), [DAU Target])
``````

Hello @Max,

Thank you for posting your query onto the Forum.

Well it’s always difficult to judge the results in the absence of PBIX file. But still I think from the given screenshot provided by you is as follows -

1. The figures portrayed from “Jan - July” are the months that pertains to the current year and therefore based on that it’s showing the Forecast Percentage for those months because in your formula your context is applied as "TODAY() - 1 Month" and therefore it’s giving you the result for the July month as well since we’ve already entered this month.

2. Now for figures for the period from “August - December”, this figures might be pertaining to the last year and therefore it’s not able to showcase the results for those periods since we haven’t entered into those periods. But if you apply filter/slicers for the years such as “Current Year” and “Previous Year” those blank rows will get removed and you’ll see the results only for the period till date.

3. Alternatively, if you’re following the updated “Date” table which is already available in the forum in that case you can use the “IsAfterToday” column to filter out your results and to show the results only for the current period till date.

This similar type of problem was asked few days back onto the Forum by one of the member and it used to show similar sort of results. I’m providing a link of that query as well as working of my PBIX file about how we were able to tackle that problem by incorporating the 3rd option.

Hoping you find this useful and helps you to achieve the desired analysis.

Please feel free to write back in case I’ve missed out on anything.

Thanks & Warm Regards,
Harsh

Example Data (1).pbix (334.3 KB)

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.

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.

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

1 Like