Require help to calculate Cumulative Recognised Revenue until prior month

[Sunila]

Hi, 

I'm seeking assistance with a DAX issue where I'm attempting to incorporate previously recognized revenues in the current month's report. Specifically, when a user is selecting January dates from date slicer having start date and end date,  the following cumulative calculations need to be added up in the current month's calculations :

1. Prior Cumulative % Complete (highlighted in sky blue in the attached Excel, 67%)
2. Prior Month Cumulative Cost (Actual + Committed Cost)
3. Prior Month Recognized Cost (PriorMnthCumm*Prev%Comp that is point 1* 2)
4. Current Month Recognized Cost ((1-Prev %Comp)*(Actual cost +Committed Cost)

In essence, I'm looking to create measures that capture the calculations mentioned. Points 3 and 4 involve deriving results from measures created for points 1 and 2, so the primary focus is on developing measures for the calculations related to 1 and 2.

Any prompt help or guidance would be greatly appreciated. Thank you! 

Note: Unfortunately, I couldn't attach the Excel sheet, so I've included a screenshot instead. The data above the orange line represents the prior month's data, while the data below the orange line pertains to the current month. I hope this visual aid proves helpful!

[v-jiewu-msft]

Hi @Sunila ,

If I understand correctly, the issue is that you want to calculate recognized revenue until prior month. Please try the following methods and check if they can solve your problem:

1.Create a measure that sums the percentage of the previous month.

Prior Cumulative % Complete =
CALCULATE (
    SUM ( Table1[Cost] ) / SUM ( Table1[Revenue] ),
    FILTER (
        ALLSELECTED ( Table1[Date] ),
        Table1[Date] <= MIN ( Table1[Date] )
    )
)

2.For the cumulative cost of the prior month, use the following DAX formula.

Prior Month Cumulative Cost = 
CALCULATE (
    SUM ( Table1[Cost] ),
    FILTER (
        ALLSELECTED ( Table1[Date] ),
        Table1[Date] >= EOMONTH ( MIN ( Table1[Date] ), -1 ) &&
        Table1[Date] < MIN ( Table1[Date] )
    )
)

3.For the Prior Month recognized cost, use the following DAX formula.

Prior Month Recognized Cost = 
[Prior Month Cumulative Cost] * [Prior Cumulative % Complete]

4.For the current month recognized cost, use the following DAX formula.

Current Month Recognized Cost = 
(
    1 - [Prior Cumulative % Complete]
) * (
    SUM ( Table1[Cost] ) + SUM ( Table1[Committed Cost] )
)

Best Regards,

Wisdom Wu

[Sunila]

Hi @v-jiewu-msft 

Calculating Delivered Rev based on your suggested dAX for cost, unfortunately, does not provide the expected results

Delivered Rev Cumulative =

CALCULATE (

    [DeliveredRevSum],

    Filter(

    ALLSELECTED('Delivered Revenue'[LEDGERTRANSDATE]),

    'Delivered Revenue'[LEDGERTRANSDATE] >= EOMONTH([Selected Start Date],-1)) &&

        'Delivered Revenue'[LEDGERTRANSDATE] < [Selected Start Date] )

Lets take selected user date as 01/01/2024 so as per the suggested DAX, it would capture data equal to or greater then 31/12/2023 which does not solve the purpose. I need cumulative rev until prior month. 

[123abc · Accepted solution]

Here's a general approach to creating these measures:

1. Prior Cumulative % Complete (Point 1):

   • This measure calculates the cumulative percentage complete until the end of the previous month.
   • You can use DAX functions like TOTALYTD or DATESYTD to calculate the cumulative total.

2. Prior Month Cumulative Cost (Actual + Committed Cost):

   • Create a measure that sums up the Actual and Committed costs for the prior month.

3. Prior Month Recognized Cost (PriorMnthCumm * Prev % Comp):

   • Multiply the Prior Month Cumulative Cost by the Prior Cumulative % Complete.

4. Current Month Recognized Cost ((1 - Prev % Comp) * (Actual cost + Committed Cost)):

   • Calculate the remaining portion of the current month's costs based on the percentage complete for the current month.

Here's how you might implement these measures in DAX:

Prior Cumulative % Complete =
CALCULATE(
[Cumulative % Complete],
FILTER(
ALL('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date]) - 1
)
)

Prior Month Cumulative Cost =
CALCULATE(
[Actual Cost] + [Committed Cost],
FILTER(
ALL('Date'[Date]),
'Date'[Date] >= STARTOFMONTH(MAX('Date'[Date])) - 1 &&
'Date'[Date] < STARTOFMONTH(MAX('Date'[Date]))
)
)

Prior Month Recognized Cost = [Prior Month Cumulative Cost] * [Prior Cumulative % Complete]

Current Month Recognized Cost =
(
1 - [Prior Cumulative % Complete]
) * (
[Actual Cost] + [Committed Cost]
)

Please adjust the measures according to your data model and requirements. Ensure that you have the necessary date and cost columns in your data model to perform these calculations effectively.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Sunila]

@123abc 

I am focusing on calculating cost for now. I did try substituting your DAX cost, but unfortunatley it does not seem to be working as expected. Please see my above comment.