Forum Discussion

PoweredOut's avatar
PoweredOut
Icon for Resolver I rankResolver I
3 years ago

Calculate Revenue Per Month between two dates

Hello   I want to visualise Revenue in a matrix based on the Revenue start date and end date. Let say I have Revenue of 100,000, which appears on the Revenue Start Date. Revenue starts in January a...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

Sounds straightforward. What have you tried and where are you stuck?

JB_AT's avatar
JB_AT
Icon for Helper III rankHelper III
2 years ago

lbendlin 

I have a similar issue. I need to split the Won Opportunity Amount over the months of the Project Start and End dates. I have wokred out the monthly amount based on the project duration and have added it as a calculated column. I would like to have a similar visual above, where the monthy amount populates each month of the duration of the project and shows the total of these months in the total section

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • JB_AT's avatar
      JB_AT
      Icon for Helper III rankHelper III
      2 years ago

      Thanks for your reply lbendlin 

       

      Here is a sample of the Opportunity table

       

      RevenueStartDateEndDateTotalMonthsMonthly Revenue
      100,00001/02/202431/01/2025128,333


      I would like to have a visual with the the Year and Month columns from my Date Table and the Monthly Revenue populated in the months where the Start and EndDates are valid, Date table has a one to many active relationship with the StartDate.

      I ahve created a measure which populates the month, it's a running total, but it doesn't sum up in the visual

       

       

      VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM(Opps[MonthlyRevenue]),
        KEEPFILTERS ( Opps[StartDate] <= MaxDate
            && Opps[EndDate] >= MaxDate
            )
            ,
        ALL ( 'Date')
)

       

       

       

      Thanks in advance