Turning Formula Dynamic

[Anonymous]

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!

[jcarville]

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.

[Anonymous]

@jcarville How would I incorporate that into my formula?

[jcarville]

@Anonymous, apologies, I thought you were using M but can see you are using DAX. My bad!

[Anonymous]

@jcarville no problem! I feel I'm not to far off from my answer. Just not sure how to finish it out.

[v-jiascu-msft · Accepted solution]

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

[Anonymous]

@v-jiascu-msft works perfect! Thank you Dale.