Problem with Formula

[Fools_Gold]

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 below:

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]),MONTH('Custom Report 6'[DataType_2])=MONTH([First Day 4 Months Ago]))

Formula's Used:

DataType_2 = 7/1/2024, 8/1/2024, etc.

First Day of Month 3 Months Ago = First Day of Month 3 Months Ago = DATE(YEAR([First day of Month Current Month]),(MONTH([First day of Month Current Month])-3),DAY([First day of Month Current Month]))

End of Month 3 Months Ago = End of Month 3 Months Ago = EOMONTH(TODAY(),-3)

I get the following error: "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Thank you,

Fools_Gold

[chovatiya_parth]

Hi Fools_Gold,

Here is the updated Measure that you can use, which works very efficiently in terms of performance.

Lock 4 Months Ago for 3 Months Ago Rolling =
CALCULATE(
                   SUM('Custom Report 6'[SalesQty]), -- Sum SalesQty
                   DATESBETWEEN(
                                          'Custom Report 6'[MonthStart], -- Filter on MonthStart column
                                           [First Day of Month 3 Months Ago], -- Start date of range
                                           [End of Month 3 Months Ago] -- End date of range
                                           ),
                     MONTH('Custom Report 6'[DataType_2]) = MONTH([First Day 4 Months Ago]) -- Match the month condition
)

Also, make sure that the below 3 measures are calculated correctly,

First Day of Month 3 Months Ago =
DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1)

End of Month 3 Months Ago =
EOMONTH(TODAY(), -3)

First Day 4 Months Ago =
DATE(YEAR(TODAY()), MONTH(TODAY()) - 4, 1)

Thanks & Regards,
Parth Chovatiya

[Fools_Gold · Accepted solution]

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))))

[anmolmalviya05]

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])
)
)

[Fools_Gold]

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

[Anonymous]

Hi @Fools_Gold ,

1. We noticed that you used the following statement. The results will return negative numbers when the current month is between January and March:

Please try this:

First Day of Month 3 Months Ago = EOMONTH(TODAY(),-4) + 1

A negative value yields a past date in the EOMONTH function syntax.

2. If we want to filter a column in a CALULATE function, we could:

• Compare the column to a static value.
• Use variables to create a static value.
• Use a FILTER function instead of a true/false expression.

3. Please try this:

Lock 4 Months Ago for 3 Months Ago Rolling =
VAR __start_date = [First Day of Month 3 Months Ago]
VAR __end_date = [End of Month 3 Months Ago]
VAR __month =
    MONTH ( [First Day 4 Months Ago] )
VAR __result =
    CALCULATE (
        SUM ( 'Custom Report 6'[SalesQty] ),
        DATESBETWEEN ( 'Date'[Date], __start_date, __end_date ),
        'Date'[Month] = __month
    )
RETURN
    __result

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fools_Gold]

Hello @Anonymous,

I tried your formula and got the error below:

I have a column named Datatype_2 that needs to match the _month.

Datatype_2 is just dates.

I also copied your formula for First Day 3 Months Ago and named it New, since I already a similar formula.

New First Day of Month 3 Months Ago = EOMONTH(TODAY(),-4) + 1

I tried this one as well:

Any further help would be greatly appreciated.

Thank you!

Fools_Gold

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Fools_Gold]

Hello @Ashish_Mathur,

I do not have one currently available.

Thank you,

Fools_Gold