Creating a forward forecast measure

[dham1012]

Hi,

I'm trying to create an invoice forward forecast in powerbi, looks something like this (the values are wrong).

PowerBi Desktop file 

I have a table of quote lines (bidata table in the attached) which have start dates (start_date_calc) and recurrence periods (line_recurrence(line_recurrence).months).

So for each line in the bidata table I want to sum 'total_sell', but only if it is going to bill in the period of the row in _aa_BI_contractBank[DateNow].

In excel I can get the values by adding columns for each month and determine if the line item should bill, see example below.

What I'd like to get is the totals for each of the excel months columns in the contrackBank table as rows against each month (as per the first image)

I've tried to replicate this formula in a calculated column but with no success. 

The formula for the column in the contractBank table is as follows

Opportunity Contract Bank value = 

calculate(sum(bidata[total_sell (monthly/onetime)]),
    filter(bidata, 
            bidata[start_date_calc] = if(
                DATEDIFF(EOMONTH(bidata[start_date_calc],-1)+1,EOMONTH(_aa_BI_contractBank[DateNow],-1)+1,MONTH)/bidata[line_recurrence(line_recurrence).months] = 
            INT(DATEDIFF(EOMONTH(bidata[start_date_calc],-1)+1,EOMONTH(_aa_BI_contractBank[DateNow],-1)+1,MONTH)/bidata[line_recurrence(line_recurrence).months]), _aa_BI_contractBank[DateNow], 0)
            && bidata[start_date_calc] < eomonth(_aa_BI_contractBank[DateNow], 0)
            && bidata[start_date_calc] > eomonth(NOW(), -1)
            && bidata[_opportunity_status] < 5
            && bidata[deleted] = false()  
            && not(bidata[_Revenue Type] = "One-time")))

Any help would be greatly appreciated.

Thanks

[dham1012]

Hi @v-easonf-msft,

Comparing the results from what I have in excel vs BI something is not calculating corectly. It seems the current months is always correct, but determining future months isn't.

I'll try and explain the calculation from the bidata table screenshot below

From the example above using the start_date_calc and line_recurrance the first line would invoice in september and then every 12 months. The 3rd line down would also bill in Spetember and then every 3 months. 

So in this example using the contractBank[DateNow] value I would want those items included in that total for September, and then the first item would be included in Spetember 2022, for the 3rd item it would be included in December 2021, March 2022, June 2022, September 2022 etc.

Hope that makes some sense.

Thanks

[v-easonf-msft]

Hi,  @dham1012 

I don't find any obvious syntax errors in your formula, you may need to recheck those parallel filter conditions.

I'm not sure what your mean by 'the period of the row in _aa_BI_contractBank[DateNow]'.For further research ,can please give more details on which records in the bigdata table should be computed for a known DataNow.

Best Regards,
Community Support Team _ Eason

[dham1012]

Hi @v-easonf-msft , did my reply (I must have replied to the topic not your question) make any sense and do you have any ideas how i can make this work?

[v-easonf-msft]

Hi, @dham1012 

Due to my limited ability, I don’t have any ideas on this issue.

Hopefully other members of the community can provide feasible ideas.

Best Regards,
Community Support Team _ Eason