Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Forecast Growth with PRODUCTX()

I'm trying to forecast based off a variable growth rate built from a "What if" parameter. I've been following this helpful blog Recursive Calculation in Power BI and I've gotten to the final steps an...
Need Help
v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous ,

 

By my test, you need to modify the measure “LastMembership”. The following is my sample you can reference, then to modify yours.

 

I have three tables. Table DimDate is a calendar table.  There is a many-to-one relationship between table FactMembership and table DimDate.

 

DimDate = CALENDARAUTO()

Month = MONTH(DimDate[Date])

 

Note : Create the measure LastMembership using SUM function, not COUNTROWS function.

LastMembership =

IF(

    ISBLANK(CALCULATE(SUM(FactMembership[Members]), DATEADD(DimDate[Date], 1, MONTH))),

    SUM(FactMembership[Members]),

    1

)

GrowthRate Value = CALCULATE(VALUES(ExpectedGrowth[ExpectedGrowth]), LASTDATE(ExpectedGrowth[Month]))

 

MultiplyBy = IF(ISBLANK([LastMembership]), 1+[GrowthRate Value], [LastMembership])

 

TotalMembers = SUM(FactMembership[Members])

 

ForecastMembership =

IF(ISBLANK(FactMembership[TotalMembers]),

    CALCULATE(

        PRODUCTX(VALUES(DimDate[Month]), [MultiplyBy]), DATESBETWEEN(DimDate[Date], BLANK(), MAX(DimDate[Date]))

    ),

    [TotalMembers]

)

 

Best Regards,

Xue Ding

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks for the in-depth reply! Sadly, that doesn't seem to be the issue. My FactMembership table is a snapshot of my company's membership on a monthly basis where each row is a person and frozen at the end of each month. I can't really sum up the rows but I got around this by adding a caculated column called "members" that just equals 1 and summed that:

LastMembership = 
IF(
    ISBLANK(CALCULATE(SUM(FactMembership[Members]), DATEADD(DimDate[Date], 1, MONTH))), 
    SUM(FactMembership[Members]), 
    1
)

 I've had problems before with time based functions before so I decided to re-create your setup as closely as possible and ended up with this data model using CALENDARAUTO().

 

 

Sadly I end up with the same issue with all formulas unchanged except for TotalMembers and LastMembership which I switched to SUM():

Let me know if you can think of anything else. Thanks v-xuding-msft