Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
StevenCollins
Frequent Visitor

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 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?

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1657005109632.png

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.

vkalyjmsft_1-1657005234705.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1657005109632.png

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.

vkalyjmsft_1-1657005234705.png

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.

Thank you, that worked perfectly

thiagocamillo
Helper I
Helper I

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

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)
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.