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])
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.
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 -
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.
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.
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.
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:
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.
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!