Forum Discussion

FlightofICARUS's avatar
FlightofICARUS
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    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.

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
4 years ago

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.