powerbidaxfinancial

Project revenue calculation in DAX

I need to calculate the ongoing revenue for installation + maintenance for projects and calculate the monthly revenue for controlling purposes in DAX in Power BI.

The problem is the following.

The projects are stored in a table CONTRACTS like this:

enter image description here

And I have a separate date table INST_DATE_TABLE:

enter image description here

The tables are connected via the [INSTALLATION_DATE] field.

enter image description here

For every month the revenue is the total of the [INSTALLATION_REVENUE] if the installation was carried out that month plus from the first month on the monthly maintenance revenue which is given as the [MAINTENANCE_COST_PER_UNIT] * [MAINTENANCE_UNIT] / 12.

And the maintenance revenue should be only calculated if the current date is beyond the installation date!

Some contracts are not yet signed, so they dont't have an installation date set (NULL)

So the INSTALLATION REVENUE DAX is like this:

    .INSTALLATION_REVENUE = 
     CALCULATE (
        SUMX(CONTRACTS; 
        CONTRACTS[INSTALLATION_REVENUE] 
            );
        CONTRACTS[INSTALLATION_DATE] > 0
     )

And the MONTHLY REGULAR REVENUE is like this:

    .REGULAR_REVENUE = 
    CALCULATE ( 
       SUMX(CONTRACTS; 
       CONTRACTS[MAINTENANCE_COST_PER_UNIT]*CONTRACTS[MAINTENANCE_UNIT]
        ) / 12;
        CONTRACTS[INSTALLATION_DATE] > 0
     )

For all dates I can calculate the cash flow of the latter like this:

     .REGULAR_REVENUE_ONGOING = 
      CALCULATE ( 
          [.REGULAR_REVENUE];
          ALL(INST_DATE_TABLE[INSTALLATION_DATE])
      )

which gives me a nice series of monthly revenues for all periods. But I only would like to see this for the periods that are beyond the installation date!

So lets say filtered on contract 1 I have now the following cash flow:

enter image description here

But for periods before 2019.04.01 I would like to see zeros!

How can I do that?

I just can't filter on dates referring to the installation date of the project!

After I have the expected result for one contract it would be easy to sum it up for all contracts like this

      .TOTAL_REVENUE = 
      [.INSTALLATION_REVENUE] + [.REGULAR_REVENUE_EXPECTED]

UPDATE:

I created a cumulative total to display the ongoing revenue as such:

   .REGULAR_REVENUE_ONGOING = 
   CALCULATE ( 
       [.REGULAR_REVENUE];
       FILTER(
           ALL(INST_DATE_TABLE[INSTALLATION_DATE]);
           INST_DATE_TABLE[INSTALLATION_DATE
             <=MAX(INST_DATE_TABLE[INSTALLATION_DATE])
           )
     )

this displays the correct series, but now I have another problem. When I try to cumulate this already cumulative data series it does not add up as a cumulative data series!

Any help would be appreciated

   .REVENUE_TOTAL_CUMULATIVE = 
    CALCULATE(
       [.REVENUE_TOTAL];
       FILTER(
           INST_DATE_TABLE;
           INST_DATE_TABLE[INSTALLATION_DATE] <= MAX(INST_DATE_TABLE[INSTALLATION_DATE])
           )
    )

enter image description here

Solution

  • Assuming there are no end dates to your ongoing revenue, then try:

    .REGULAR_REVENUE_ONGOING = 
VAR DateMin = 
    CALCULATE(
        MIN ( CONTRACTS[INSTALLATION_DATE] ),
        ALL ( INST_DATE_TABLE )
    )
VAR DateMax = 
    MAX ( INST_DATE_TABLE[INSTALLATION_DATE] )
RETURN
    SUMX ( 
        FILTER ( 
            ALL ( INST_DATE_TABLE ),
            INST_DATE_TABLE[INSTALLATION_DATE] >= DateMin && INST_DATE_TABLE[INSTALLATION_DATE] <= DateMax
        ),
        [.REGULAR_REVENUE]
    )

    And for a cumulative total revenue:

    .REVENUE_TOTAL_CUMULATIVE = 
VAR DateCurrent = MAX ( INST_DATE_TABLE[INSTALLATION_DATE] )
VAR CumulativeInstallationRevenue = 
    CALCULATE ( 
        [.INSTALLATION_REVENUE],
        FILTER ( 
            ALL ( INST_DATE_TABLE ),
            INST_DATE_TABLE[INSTALLATION_DATE] <= DateCurrent
        )
    )
VAR CumulativeOngoingRevenue = 
    SUMX ( 
        FILTER ( 
            ALL ( INST_DATE_TABLE ),
            INST_DATE_TABLE[INSTALLATION_DATE] <= DateCurrent
        ),
        [.REGULAR_REVENUE_ONGOING]
    )
RETURN
    CumulativeInstallationRevenue + CumulativeOngoingRevenue

    enter image description here

    See https://pwrbi.com/so_55808659/ for worked example PBIX file