Forum Discussion
Calculate forecast value based on conditions (DAX)
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 |
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
1 Reply
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