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
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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!