cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Rolling 12 month sum

I am trying to create a rolling 12 month sum of the Fees and Write Off columns

I have a DateDim table related on Date to this table

=CALCULATE( [Sum of Fees]
,DATESBETWEEN(
Revenue[Date]
,SAMEPERIODLASTYEAR( NEXTDAY ( LASTDATE ( Revenue[Date] ) ) )
,LASTDATE ( Revenue[Date] )
)
)

What am i doing wrong

5 REPLIES 5
Member

## Re: Rolling 12 month sum

If you add ALL(Revenue) to the filter arguments it will work:

The revised formula is:

=CALCULATE( [Sum of Fees]

ALL(Revenue)
, DATESBETWEEN(
Revenue[Date]
, SAMEPERIODLASTYEAR( NEXTDAY ( LASTDATE ( Revenue[Date] ) ) )
, LASTDATE ( Revenue[Date] )
)
)

Frequent Visitor

## Re: Rolling 12 month sum

Hi DAX0110

I am getting an error

The syntax for 'ALL' is incorrect.

And if I modify your formula to include a filter expression.

=CALCULATE( [Sum of Fees]
FILTER( ALL(Revenue)
,DATESBETWEEN( Revenue[Date]
,SAMEPERIODLASTYEAR( NEXTDAY ( LASTDATE ( Revenue[Date] ) ) )
,LASTDATE ( Revenue[Date] )
)
)
)

I get an error "Column 'Sum of Fees' cannot be found or may not be used in this expression.

Regards

Member

Super User

## Re: Rolling 12 month sum

Hi @MicHamo,

Super Contributor

## Re: Rolling 12 month sum

Hi @MicHamo,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here. If you haven't resolve it, please respond to us and post the required information.

Thanks,
Angelia

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 60 members 1,290 guests
Recent signins: