cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Measure help

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.
Community Support

hi, @Anonymous

Best Regards,

Lin

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