Show GM Forecast based on Contract Dates

[learningpp]

I am trying to graph a gross margin forecast by project for the next 6 to 12 months based on the contract dates of the projects. My calendar table is setup by date (i.e. 1/1/2014, 1/2/2014, etc). I then have a contract tab with the beg date and end date. I want to base the GM forecast on the average GM which I have calculated in a measure for this year. I have tried several formulas and keep getting errors.

Here are the formulas that I tried and I keep getting errors:

=CALCULATE([AvgGM],Contracts[Beg Date]>Calendar2014[Date]&&Contracts[End Date],Calendar2014[Date])

=if(Contracts[Beg Date]>Calendar2014[Date]&&Contracts[End Date]<Calendar2014[Date],CALCULATE([AvgGM]),0)

Thanks!

 

[scottsen]

These problems are a bit more interesting than your average problem

Can you tell me what your AvgGM measures looks like, and a bit of sample data? I do think you are flirting w/ the correct idea.

It's at least... vaguely similiar to a "Person Hours" type of problem in that you are doing a calculation across a date window. You might see if this gives you any ideas: Two methods to calculate Person-Hours | Power Pivot | Tiny Lizard -- comments send you off to other similiar problems as well.

 

[learningpp]

Thanks for the response. The data is transaction detail downloaded from Quickbooks. I have one table with just the data that has date, g/l account number, debits, credits, etc. I have a reference table which assigns each g/l account to either Revenue, COGS, Other Expense, etc. as well as to a project. I then have a contracts table with the project name, beg date and end date. I am just struggling with mixing fact and forecast.

Here are my formulas:
AvgGM:=IF(ISFILTERED(Calendar2014[AvgRow]),AVERAGEX(VALUES(Calendar2014[MonthNumberOfYear]),[GM All]),[GM All])

GM ALL:=calculate([Rev All]-[COGS ALL])

COGS ALL:=calculate([RawExpense],DataAll[GL Category]="COGS")

Rev All:=calculate([RawRevenue],DataAll[GL Category]="Revenue")