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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MicHamo
Regular 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
Ashish_Mathur
Super User
Super User

Hi @MicHamo,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DAX0110
Resolver V
Resolver V

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

 

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.