Rolling 12 month sum

[MicHamo]

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

[Ashish_Mathur]

Hi @MicHamo,

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

[DAX0110]

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]

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

[v-huizhn-msft]

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

[DAX0110]

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