Help needed : Dax calculate table
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]))