DAX code to calculate Remaining Revenue

[FlightofICARUS]

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

[FlightofICARUS]

Hi Thank you for the reply, it wasn't exactly what I needed but it guided me in the right direction 🙂

[v-jianboli-msft · Accepted solution]

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.