Problem with Formula

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.

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.

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

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