Forum Discussion
Solved
Problem with Formula
Hello, I am trying to calcuate a forecast to look at the next month. In this instance, I am trying to calculate a forecast 4 months ago to look for 3 months ago. The calculated column I am using is ...
Thank you for everyone's help. I solved it. See below:
Lock 4 Months Ago for 3 Months Ago Rolling Test #3 =CALCULATE(SUMX('Custom Report 6','Custom Report 6'[SalesQty]),DATESBETWEEN('Custom Report 6'[MonthStart],[First Day of Month 3 Months Ago],[End of Month 3 Months Ago]), FILTER('Custom Report 6',(DATE(YEAR('Custom Report 6'[DataType_2]),MONTH('Custom Report 6'[DataType_2]),1)=DATE(YEAR([First Day 4 Months Ago]),MONTH([First Day 4 Months Ago]),1))))
Hi Fools_Gold, Please try the below measure:
Lock 4 Months Ago for 3 Months Ago Rolling =
CALCULATE(
SUMX('Custom Report 6', 'Custom Report 6'[SalesQty]),
DATESBETWEEN(
'Custom Report 6'[MonthStart],
[First Day of Month 3 Months Ago],
[End of Month 3 Months Ago]
),
FILTER(
'Custom Report 6',
MONTH('Custom Report 6'[DataType_2]) = MONTH([First Day 4 Months Ago])
)
)
Hello anmolmalviya05,
I tried yours, but it is summing it crazily.
It should be 2,079,076 not 1,211,481,678,220.
Any further help would be greatly appreciated.
Thank you!
Fools_Gold