Measure help

[Anonymous]

Hello,

I have recently taken over a Power BI report and am trying to deconstruct some formulas. I am trying to calculate a Revenue Backlog (Total Contract Value - Monthly Cumulative Revenue) for each given month to get a waterfall schedule. 

Currently it is formulated as such:

Rev Backlog (2018-01) 3 =

// Be mindful of the date sensitivities.

IF(AND(

EOMONTH(DATE(2018, 1, 1), 0) >=

EOMONTH((SUM(Invoice[Opportunity Close Date]) / COUNT(Invoice[Opportunity Close Date])), 0),

CALCULATE(SUM('Invoice'[Monthly_Revenue__c]),

FILTER(Invoice, Invoice[Invoice_Date__c] <= EOMONTH(DATE(2018, 1, 1), 0))) = 0),

SUM(Invoice[Monthly_Revenue__c]),

IF(OR(

CALCULATE(COUNTAX(Invoice, Invoice[Invoice Date Not Blank] = "TRUE"),

FILTER(Invoice, EOMONTH(Invoice[Invoice_Date__c], 0) = EOMONTH(DATE(2018, 1, 1), 0))) <> 1,

// If there is no revenue data

SUM(Invoice[Monthly_Revenue__c])

- CALCULATE(SUM('Invoice'[Monthly_Revenue__c]),

FILTER(Invoice, Invoice[Invoice_Date__c] <= EOMONTH(DATE(2018, 1, 1), 0))) < 0.5),

// Total Expected Revenue - Actual Cumulative Revenue (as of EOM Date)

BLANK(),

SUM(Invoice[Monthly_Revenue__c])

- CALCULATE(SUM('Invoice'[Monthly_Revenue__c]),

FILTER(Invoice, Invoice[Invoice_Date__c] <= EOMONTH(DATE(2018, 1, 1), 0)))))

// Total Expected Revenue - Actual Cumulative Revenue (as of EOM Date)))

The formula does not seem to pick up on the first month of the revenue recognition: ie. a contract with a 120k value over 12 months would recognize 10k per month, the formula is displaying a backlog of 0 on the first month instead of 110k (120k - 10k).

Any help would be greatly appreciated. 

[v-lili6-msft]

hi, @Anonymous 

Sample pbix file would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin