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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Turning Formula Dynamic

I have this formula and table;

1.PNG

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!

1 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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
jcarville
Skilled Sharer
Skilled Sharer

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
Not applicable

@jcarville How would I incorporate that into my formula?

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

Anonymous
Not applicable

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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