Forum Discussion
dax help
im not able to achieve rolling sum values for below dax
test2 =
var endmonth = SELECTEDVALUE(W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM])
var startmonth = endmonth-11
RETURN
CALCULATE(SUM(HR_FACT[_Terminated Voluntary Employee OHR]), HR_FACT[Gender] = "Female"&&HR_FACT[TERMINATION_REASON] <> "Retirement", 'W_MCAL_PERIOD_D'[MCAL_JULIAN_PERIOD_NUM] >= startmonth,ALL(W_MCAL_PERIOD_D ) )
any help.
need to get rolling sum values for above screenshot
4 Replies
Anonymous
test2 =
VAR EndMonth = SELECTEDVALUE(W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM])
VAR StartMonth = EndMonth - 11
RETURN
CALCULATE(
SUM(HR_FACT[_Terminated Voluntary Employee OHR]),
HR_FACT[Gender] = "Female",
HR_FACT[TERMINATION_REASON] <> "Retirement",
W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM] >= StartMonth &&
W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM] <= EndMonth,
ALL(W_MCAL_PERIOD_D)
)💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHere is an improved DAX formula to calculate the rolling sum:
test2 =
VAR CurrentMonth = SELECTEDVALUE(W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM])
VAR StartMonth = CurrentMonth - 11
RETURN
CALCULATE(
SUM(HR_FACT[_Terminated Voluntary Employee OHR]),
HR_FACT[Gender] = "Female",
HR_FACT[TERMINATION_REASON] <> "Retirement",
FILTER(
ALL(W_MCAL_PERIOD_D),
W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM] >= StartMonth &&
W_MCAL_PERIOD_D[MCAL_JULIAN_PERIOD_NUM] <= CurrentMonth
)
)
