Description
The expression below is used to determine Period# for when Earnings start becoming positive.
Earnings are aggregated for each period and all periods preceding that period and then compared >=0.
If condition is true then the corresponding period (1-100) is set by the "If",otherwise Null().
RangerMin() returns, by default, the first non-Null value.
This expression is used in pivot tables with varying number of dimensions and provides accurate results, including when selections are applied.
Prior to creating the RangeMin() expression (assigned to a variable in script for convenience) I was pre-calculating the value in the script at the lowest dimension granularity - but found that totals were not accurate when using averaging in the expression.
Challenge
Is there a simpler way to write this? I tried using Aggr() but couldn't get it to work.
Thanks in advance for any suggestions!
RangeMin(
If(Sum({$<[Period]={"<=001"}>}[Earnings])>=0,1),
If(Sum({$<[Period]={"<=002"}>}[Earnings])>=0,2),
If(Sum({$<[Period]={"<=003"}>}[Earnings])>=0,3),
If(Sum({$<[Period]={"<=004"}>}[Earnings])>=0,4),
If(Sum({$<[Period]={"<=005"}>}[Earnings])>=0,5),
If(Sum({$<[Period]={"<=006"}>}[Earnings])>=0,6),
If(Sum({$<[Period]={"<=007"}>}[Earnings])>=0,7),
If(Sum({$<[Period]={"<=008"}>}[Earnings])>=0,8),
If(Sum({$<[Period]={"<=009"}>}[Earnings])>=0,9),
If(Sum({$<[Period]={"<=010"}>}[Earnings])>=0,10),
If(Sum({$<[Period]={"<=011"}>}[Earnings])>=0,11),
If(Sum({$<[Period]={"<=012"}>}[Earnings])>=0,12),
If(Sum({$<[Period]={"<=013"}>}[Earnings])>=0,13),
If(Sum({$<[Period]={"<=014"}>}[Earnings])>=0,14),
If(Sum({$<[Period]={"<=015"}>}[Earnings])>=0,15),
If(Sum({$<[Period]={"<=016"}>}[Earnings])>=0,16),
If(Sum({$<[Period]={"<=017"}>}[Earnings])>=0,17),
If(Sum({$<[Period]={"<=018"}>}[Earnings])>=0,18),
If(Sum({$<[Period]={"<=019"}>}[Earnings])>=0,19),
If(Sum({$<[Period]={"<=020"}>}[Earnings])>=0,20),
If(Sum({$<[Period]={"<=021"}>}[Earnings])>=0,21),
If(Sum({$<[Period]={"<=022"}>}[Earnings])>=0,22),
If(Sum({$<[Period]={"<=023"}>}[Earnings])>=0,23),
If(Sum({$<[Period]={"<=024"}>}[Earnings])>=0,24),
If(Sum({$<[Period]={"<=025"}>}[Earnings])>=0,25),
If(Sum({$<[Period]={"<=026"}>}[Earnings])>=0,26),
If(Sum({$<[Period]={"<=027"}>}[Earnings])>=0,27),
If(Sum({$<[Period]={"<=028"}>}[Earnings])>=0,28),
If(Sum({$<[Period]={"<=029"}>}[Earnings])>=0,29),
If(Sum({$<[Period]={"<=030"}>}[Earnings])>=0,30),
If(Sum({$<[Period]={"<=041"}>}[Earnings])>=0,41),
If(Sum({$<[Period]={"<=042"}>}[Earnings])>=0,42),
If(Sum({$<[Period]={"<=043"}>}[Earnings])>=0,43),
If(Sum({$<[Period]={"<=044"}>}[Earnings])>=0,44),
If(Sum({$<[Period]={"<=045"}>}[Earnings])>=0,45),
If(Sum({$<[Period]={"<=046"}>}[Earnings])>=0,46),
If(Sum({$<[Period]={"<=047"}>}[Earnings])>=0,47),
If(Sum({$<[Period]={"<=048"}>}[Earnings])>=0,48),
If(Sum({$<[Period]={"<=049"}>}[Earnings])>=0,49),
If(Sum({$<[Period]={"<=050"}>}[Earnings])>=0,50),
If(Sum({$<[Period]={"<=051"}>}[Earnings])>=0,51),
If(Sum({$<[Period]={"<=052"}>}[Earnings])>=0,52),
If(Sum({$<[Period]={"<=053"}>}[Earnings])>=0,53),
If(Sum({$<[Period]={"<=054"}>}[Earnings])>=0,54),
If(Sum({$<[Period]={"<=055"}>}[Earnings])>=0,55),
If(Sum({$<[Period]={"<=056"}>}[Earnings])>=0,56),
If(Sum({$<[Period]={"<=057"}>}[Earnings])>=0,57),
If(Sum({$<[Period]={"<=058"}>}[Earnings])>=0,58),
If(Sum({$<[Period]={"<=059"}>}[Earnings])>=0,59),
If(Sum({$<[Period]={"<=060"}>}[Earnings])>=0,60),
If(Sum({$<[Period]={"<=061"}>}[Earnings])>=0,61),
If(Sum({$<[Period]={"<=062"}>}[Earnings])>=0,62),
If(Sum({$<[Period]={"<=063"}>}[Earnings])>=0,63),
If(Sum({$<[Period]={"<=064"}>}[Earnings])>=0,64),
If(Sum({$<[Period]={"<=065"}>}[Earnings])>=0,65),
If(Sum({$<[Period]={"<=066"}>}[Earnings])>=0,66),
If(Sum({$<[Period]={"<=067"}>}[Earnings])>=0,67),
If(Sum({$<[Period]={"<=068"}>}[Earnings])>=0,68),
If(Sum({$<[Period]={"<=069"}>}[Earnings])>=0,69),
If(Sum({$<[Period]={"<=070"}>}[Earnings])>=0,70),
If(Sum({$<[Period]={"<=071"}>}[Earnings])>=0,71),
If(Sum({$<[Period]={"<=072"}>}[Earnings])>=0,72),
If(Sum({$<[Period]={"<=073"}>}[Earnings])>=0,73),
If(Sum({$<[Period]={"<=074"}>}[Earnings])>=0,74),
If(Sum({$<[Period]={"<=075"}>}[Earnings])>=0,75),
If(Sum({$<[Period]={"<=076"}>}[Earnings])>=0,76),
If(Sum({$<[Period]={"<=077"}>}[Earnings])>=0,77),
If(Sum({$<[Period]={"<=078"}>}[Earnings])>=0,78),
If(Sum({$<[Period]={"<=079"}>}[Earnings])>=0,79),
If(Sum({$<[Period]={"<=080"}>}[Earnings])>=0,80),
If(Sum({$<[Period]={"<=081"}>}[Earnings])>=0,81),
If(Sum({$<[Period]={"<=082"}>}[Earnings])>=0,82),
If(Sum({$<[Period]={"<=083"}>}[Earnings])>=0,83),
If(Sum({$<[Period]={"<=084"}>}[Earnings])>=0,84),
If(Sum({$<[Period]={"<=085"}>}[Earnings])>=0,85),
If(Sum({$<[Period]={"<=086"}>}[Earnings])>=0,86),
If(Sum({$<[Period]={"<=087"}>}[Earnings])>=0,87),
If(Sum({$<[Period]={"<=088"}>}[Earnings])>=0,88),
If(Sum({$<[Period]={"<=089"}>}[Earnings])>=0,89),
If(Sum({$<[Period]={"<=090"}>}[Earnings])>=0,90),
If(Sum({$<[Period]={"<=091"}>}[Earnings])>=0,91),
If(Sum({$<[Period]={"<=092"}>}[Earnings])>=0,92),
If(Sum({$<[Period]={"<=093"}>}[Earnings])>=0,93),
If(Sum({$<[Period]={"<=094"}>}[Earnings])>=0,94),
If(Sum({$<[Period]={"<=095"}>}[Earnings])>=0,95),
If(Sum({$<[Period]={"<=096"}>}[Earnings])>=0,96),
If(Sum({$<[Period]={"<=097"}>}[Earnings])>=0,97),
If(Sum({$<[Period]={"<=098"}>}[Earnings])>=0,98),
If(Sum({$<[Period]={"<=099"}>}[Earnings])>=0,99),
If(Sum({$<[Period]={"<=100"}>}[Earnings])>=0,100)
);
