Forum Discussion
DAX code to calculate Remaining Revenue
Hi Everyone
I need help with a DAX code to calculate the Remaining Forecast Revenue.
To get to this calcualtion I need to Calculate the Actuals up until July 2022 and add the Revenue Forecast amounts from 01 Aug 2022 onwards.
i.e Actuals To Date + Revenue Forecast from following month onward
Both columns exist in the same table (ProjectOverview) and has a relationship to a Date Dimension table (BS_Calendar).
Thanks in advance
Hi FlightofICARUS ,
Please try:
Measure = VAR _actual = SUMX ( FILTER ( ALL ( ProjectOverview ), [Date] <= MAX ( 'BS_Calendar'[Date] ) ), [Actuals] ) VAR _forecast = SUMX ( FILTER ( ALL ( ProjectOverview ), [Date] > MAX ( 'BS_Calendar'[Date] ) ), [Forecast] ) RETURN IF ( MAX ( 'BS_Calendar'[Date] ) <= DATE ( 2022, 7, 1 ), BLANK (), _actual + _forecast )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-jianboli-msftCommunity Support
Hi FlightofICARUS ,
Please try:
Measure = VAR _actual = SUMX ( FILTER ( ALL ( ProjectOverview ), [Date] <= MAX ( 'BS_Calendar'[Date] ) ), [Actuals] ) VAR _forecast = SUMX ( FILTER ( ALL ( ProjectOverview ), [Date] > MAX ( 'BS_Calendar'[Date] ) ), [Forecast] ) RETURN IF ( MAX ( 'BS_Calendar'[Date] ) <= DATE ( 2022, 7, 1 ), BLANK (), _actual + _forecast )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Thank you for the reply, it wasn't exactly what I needed but it guided me in the right direction 🙂
