topic Re: Daxe issue in DAX Commands and Tips

Daxe issue

Azou — Fri, 15 Mar 2024 15:38:00 GMT

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]))

Re: Daxe issue

JamesFR06 — Fri, 15 Mar 2024 13:18:51 GMT

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]))

Re: Daxe issue

Azou — Fri, 15 Mar 2024 13:33:47 GMT

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

Re: Daxe issue

JamesFR06 — Fri, 15 Mar 2024 13:37:29 GMT

Hi,

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

Re: Daxe issue

Azou — Fri, 15 Mar 2024 13:44:15 GMT

Salut, Oui désolé. C’est vrai, mais j’avais déjà calculé cette mesure. Je ne sais pas si vous avez compris ma question sur le post précédent.

Re: Daxe issue

JamesFR06 — Fri, 15 Mar 2024 13:45:59 GMT

Donc elle fonctionne, normalement elle prend les actuals jusqu'au dernier jour ensuite elle ajoute le forecast

Re: Daxe issue

Azou — Fri, 15 Mar 2024 13:54:45 GMT

Yes. But that's not what I want right now

Re: Daxe issue

JamesFR06 — Fri, 15 Mar 2024 13:57:17 GMT

DOnc je ne comprends paq ce que tu veux désolé