Forum Discussion
Turning Formula Dynamic
I have this formula and table;
The formula works as intended as long as I hard code the Month & Year as you can see in the formula. I'm trying to set it up so it always looks back two months from the current month, sums the revenue for that month and returns the total. How can I achieve this? I don't want to have to keep updating the hardcoded date each month. Thanks!
Hi Anonymous,
Try the formula below please.
January Revenue = CALCULATE ( SUM ( 'Client Revenue'[Revenue_Amount__c] ) * 12, FILTER ( 'Client Revenue', 'Client Revenue'[Month & Year] = FORMAT ( EDATE ( TODAY (), -2 ), "mm-yyyy" ) ) )Best Regards,
Dale
6 Replies
You could use the DateTime function so it would be something along these lines in place of your hard coded bit:
DateTime.Date(DateTime.LocalNow()-2)
Not 100% if the -2 is in the correct place, or if it will go back 2 months or 2 days but it is something to look into. There are a lot of resources on the forum/web for DateTime functions looking back.
