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))))
chovatiya_parth's avatar
chovatiya_parth
Frequent Visitor
1 year 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