Forum Discussion

StevenCollins's avatar
StevenCollins
Frequent Visitor
4 years ago
Solved

Rolling 12month sum

Hi, Im trying to calculate a days cover stock position, which divides the last months stock volume by the rolling 12 months of sales. When dispalying the results in a table/graph with historical ti...
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi StevenCollins ,

    According to your description, you want to calculate sum value of the rolling 12 months based on the date you selected in the slicer, but the formula only take the selected month into calculation, you should use the removefilters

    function in the formula. 

    I create a sample.

    Modify the measure formula like this:

    Measure =
CALCULATE (
    SUM ( Combined[Sales History (Value)] ),
    DATESINPERIOD ( 'Combined'[Date], MAX ( Combined[Date] ), -12, MONTH ),
    REMOVEFILTERS ( Combined[Report Month] )
)

    Use the Report Month column in the slicer and get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

thiagocamillo's avatar
thiagocamillo
Icon for Helper I rankHelper I
4 years ago

YOU CAN USE THE "CALCULATE AND ALL" FUNCTIONS TOGETHER TO KEEP DATA RESULTS INDEPENDENT OF SLICERS

  • StevenCollins's avatar
    StevenCollins
    Frequent Visitor
    4 years ago

    Thanks, but im unsure this will work, as the filters are required to determine the rolling 12 months.

    Below is the formula im using, perhaps you can advise where ALL would be positioned please?:

    CALCULATE(
    sum(Combined[Sales History (Value)]),
    DATESINPERIOD(Combined[Report Month].[Date],MAX(Combined[Report Month]),-12,MONTH)
    )