cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MicHamo Frequent Visitor
Frequent Visitor

Rolling 12 month sum

Capture.PNG

 

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

 

My calculation reads as follows

 

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

 

What am i doing wrong

5 REPLIES 5
DAX0110 Member
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] )
         )
     )

 

MicHamo Frequent Visitor
Frequent Visitor

Re: Rolling 12 month sum

Hi DAX0110

 

Thanks for your reply.

 

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.

 

Thanks for your help

 

Regards

DAX0110 Member
Member

Re: Rolling 12 month sum

Hi @MicHamo, please add a comma before "ALL"

Super User
Super User

Re: Rolling 12 month sum

Hi @MicHamo,

 

Share the download link of your file and also show the expected result there.

Highlighted
v-huizhn-msft Super Contributor
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