Calculate forecast value based on conditions (DAX)

[DirkLX]

I have the following table and would like to create FCST values based on certain conditions but cannot get things to work. Any help is appreciated.

IF ( Region = "WEST)
     FcstValue = (Bookings value from previous period * RegionWeight) +

                      (Bookings value current period * RegionWeight)

Partner	Region	Bookvalue	Date	FcstValue	RegionWeight
A	WEST	100	12/1/2016	50	0.5
A	WEST	50	1/1/2017	75	0.5
A	WEST	50	2/1/2017	50	0.5
A	WEST	60	3/1/2017	55	0.5
B	EAST	80	12/1/2016	86	0.7
B	EAST	20	1/1/2017	70	0.7
B	EAST	20	2/1/2017	28	0.7
B	EAST	50	3/1/2017	49	0.7
C	WEST	60	12/1/2016	65	0.5
C	WEST	60	1/1/2017	60	0.5
C	WEST	80	2/1/2017	70	0.5
C	WEST	90	3/1/2017	85	0.5

[v-shex-msft · Accepted solution]

Hi @DirkLX,

Ycan try to use below measure formulas if it suitable for your requirement:

All Region:

Current + Pervious(All) =
var currPartner= LASTNONBLANK(Sheet9[Partner],[Partner])
var currRegion=LASTNONBLANK(Sheet9[Region],[Region])
var temp=FILTER(ALL(Sheet9),[Region]=currRegion&&[Partner]=currPartner)
return
SUMX(FILTER(temp,OR([Date]=MAX([Date]),[Date]=MAXX(FILTER(temp,[Date]<MAX([Date])),[Date]))),[Bookvalue]*[RegionWeight])

WEST only:

Current + Pervious(WEST) =
var currPartner= LASTNONBLANK(Sheet9[Partner],[Partner])
var temp=FILTER(ALL(Sheet9),[Region]="WEST"&&[Partner]=currPartner)
return
SUMX(FILTER(temp,OR([Date]=MAX([Date]),[Date]=MAXX(FILTER(temp,[Date]<MAX([Date])),[Date]))),[Bookvalue]*[RegionWeight])

Regards,

Xiaoxin Sheng