Forum Discussion

Fools_Gold's avatar
Fools_Gold
Icon for Helper I rankHelper I
1 year ago
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 ...
Need Help
Tips and Tricks
  • Fools_Gold's avatar
    Fools_Gold
    1 year ago

    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))))
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Fools_Gold
Icon for Helper I rankHelper I
1 year ago

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