Calculate Revenue Backlog over Time

[Anonymous]

Dear all,

I would like to calculate and visualize revenue backlog development over time. Furthermore, the backlog should be displayed by Year of Contract Award in a stack bar chart. For example, a contract value of $1M won in 2019 could be distributed over 3 years. This would mean that the backlog at the end of year 2019 would be "Total Contract Revenue - Revenue realized in 2019" and so on. The visual should look like this:

The problem which I'm struggling with is that the Backlog is calculated over the whole time period for all contract years. For example in the visual above the backlog in Year 2019 displays the backlog of Projects which were awarded in 2020 and 2021 which makes no sense as they weren't known then. Similar in Year 2020 I have my Barchart display a backlog for projects awarded in 2021. I have marked the area which should not be displayed with an "X" in the screenshot. 

My DAX Measure for the backlog looks as follows:

How can I dynamically exclude revenues from the backlog which belong to future contracts? Note: The contract Award Year is tracked in a Project Overview Table as a whole number. 

Thanks in advance!

[MFelix]

Hi @Anonymous ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

Hi @MFelix, thanks for the response and helping me out on this one. Here is a PBIX file with sample data and a quick visualisation of where I currently stand. Like mentioned above I need to account for the award year in my filter to exclude future contracts from being displayed in years prior to contract award. Hope that makes sense 😉

here is the Link to the PBIX file: Download from OneDrive 

[MFelix · Accepted solution]

Hi @Anonymous ,

In the case you have sent out yuo want to remove the marked values?

Are the totals correct?

If this is the case change your measure to:

Revenue Backlog = 
SUMX (
    FILTER (
        ProjectOverview,
        ProjectOverview[ContractAward] <= MAX ( InvoiceDates[Year] )
    ),
    CALCULATE (
        [Revenue],
        FILTER (
            ALLSELECTED ( InvoiceDates[Date] ),
            ISONORAFTER ( InvoiceDates[Date], MAX ( InvoiceDates[Date] ), ASC )
        )
    )
)

Check result below and in attach PBIX file:

[Anonymous]

it worked perfectly. Thank you!