Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Revenue Backlog over Time

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, ...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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:

     

MFelix's avatar
MFelix
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      it worked perfectly. Thank you!