topic Re: Convert excel formula to DAX in DAX Commands and Tips

Convert excel formula to DAX

Anonymous — Thu, 15 Oct 2020 08:46:26 GMT

I really really need your help converting this excel formula to Dax using columns only.

1. (Weight * PreviousMonth of Actual Volume) (1-Weight) * SmoothedForecast := These are common formulas
2. If 1/1/2019 is no previous month leave it blank
3. If date is on 2/1/2019 the value should be the 1/1/2019 as is.
4. So on my number 1 senario will perform the formulas for entire row

wdx223_Daniel apologies to mention you but I also need your experties 🙂

Re: Convert excel formula to DAX

wdx223_Daniel — Thu, 15 Oct 2020 09:13:30 GMT

Anonymous please try the code like this, you might do some adjustments to fit in your model

SmoothedForecast:=if(isempty(previousmonth(dates[date],-1,month)),blank(),if([Previousmonth],[weight]*[auctualAmount]+(1-[weight])*[Previousmonth], [auctualAmount]))

Re: Convert excel formula to DAX

Anonymous — Thu, 15 Oct 2020 09:52:16 GMT

SmoothedForecast:=if(isempty(previousmonth(dates[date],-1,month)),blank(),if([Previousmonth],[weight]*[auctualAmount]+(1-[weight])*[Previousmonth], [auctualAmount]))

wdx223_Daniel
The problems are I can't call "SmoothedForecast when the next nested if for blank forcasted Smoothed is blank. that should be the actual as is result.

Re: Convert excel formula to DAX

wdx223_Daniel — Fri, 16 Oct 2020 00:30:47 GMT

Anonymous i think you need a rescusion, but it's sadly that dax can not do it well. measuse can not reference itself.

probably you need to find another way to solve this.

Re: Convert excel formula to DAX

Anonymous — Fri, 16 Oct 2020 04:49:10 GMT

Yes I agree with you 😞 it can't be called columns result on its column..
This is sad 😞