Rolling 12month sum

[StevenCollins]

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 time intellegence, i get the correct answer. However, when slicing the table/graph to view fewer month periods, the rolling 12 month calculation only sums the cumulative values for the months displayed rather than the full 12 months thats required for the dependant days cover calculation.

Can anyone please help with a rolling 12 month DAX formula that cumulates a full 12 months regardless of what time duration is being sliced?

[v-yanjiang-msft · Accepted solution]

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.

[StevenCollins]

Thank you, that worked perfectly

[thiagocamillo]

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

[StevenCollins]

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)

)