Forum Discussion

Azou's avatar
Azou
Regular Visitor
2 years ago

Daxe issue

Hi, I need help with a DAX issue. I have a table with the following columns: YEAR MONTH, ACTUALS, and FORECAST. The YEAR MONTH table is composed as follows: 23-01; 23-02; ... ; 23-12 ; 23-01; 23-02; ... ; 23-12... And the news column has values from 23-01 to 24-02. The Forecast column has values for all months of each year. I want to calculate the sum of the actuals, but if for example there are months when the actuals are not available for a few months be replaced in the calculation of the sum by their values in Forecast. Here's a screenshot of my table. Here is the formul : 

Act = CALCULATE(sum(MasterFacts[Value]),MasterFacts[Scenario]="Actuals")
Fcst = CALCULATE(sum(MasterFacts[Value]),MasterFacts[Scenario]="Forecast")
LE MTD =
    CALCULATE(
        IF (
            SELECTEDVALUE(MasterFacts[StartDate]) <= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY())),
            [Act],
            [Fcst]
        )
    )
LE FY = CALCULATE([LE MTD],ALLEXCEPT(Dates,Dates[Date]))

7 Replies

  • JamesFR06's avatar
    JamesFR06
    Resolver IV
    2 years ago

    HI

    Measure=

    var Lastdatewithactuals= CALCULATE(max(MasterFacts[StartDate]),MasterFacts[Scenario]="Actuals")

     

    Var Act = CALCULATE(sum(MasterFacts[Value]),MasterFacts[Scenario]="Actuals")
    Var Fcst = CALCULATE(sum(MasterFacts[Value]),MasterFacts[Scenario]="Forecast")

    VAR RemainingForecast =

        CALCULATE (

            [Fcst],

            KEEPFILTERS ( MasterFacts[Value] > Lastdatewithactuals)

    var Actualandforecast=Act+Remainingforecast

    return 

    calculate(Actualandforecast,datesytd(date[date]))

    • Azou's avatar
      Azou
      Regular Visitor
      2 years ago

      Hi, thanks for your reply. Yes, the formula you sent me works well but it comes out of LE MTD in my table. What I want is to have a new measure column that will have as its value the sum that I have framed in red on the image load previously

      • JamesFR06's avatar
        JamesFR06
        Resolver IV
        2 years ago

        Hi,

        The measure I sent you id oding actuals and forecast. Please check