Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi @MicHamo,
Share the download link of your file and also show the expected result there.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |