topic Re: Rolling 12month sum in DAX Commands and Tips

Rolling 12month sum

StevenCollins — Fri, 24 Jun 2022 10:53:12 GMT

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?

Re: Rolling 12month sum

thiagocamillo — Fri, 24 Jun 2022 11:29:03 GMT

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

Re: Rolling 12month sum

StevenCollins — Fri, 24 Jun 2022 11:59:26 GMT

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)

)

Re: Rolling 12month sum

v-yanjiang-msft — Tue, 05 Jul 2022 07:14:52 GMT

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.

Re: Rolling 12month sum

StevenCollins — Wed, 06 Jul 2022 08:55:34 GMT

Thank you, that worked perfectly