Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
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!
Solved! Go to Solution.
Here's a general approach to creating these measures:
Prior Cumulative % Complete (Point 1):
Prior Month Cumulative Cost (Actual + Committed Cost):
Prior Month Recognized Cost (PriorMnthCumm * Prev % Comp):
Current Month Recognized Cost ((1 - Prev % Comp) * (Actual cost + Committed Cost)):
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.
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
Calculating Delivered Rev based on your suggested dAX for cost, unfortunately, does not provide the expected results
Here's a general approach to creating these measures:
Prior Cumulative % Complete (Point 1):
Prior Month Cumulative Cost (Actual + Committed Cost):
Prior Month Recognized Cost (PriorMnthCumm * Prev % Comp):
Current Month Recognized Cost ((1 - Prev % Comp) * (Actual cost + Committed Cost)):
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.
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.