Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 mont...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

jcarville's avatar
jcarville
Skilled Sharer
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

jcarville How would I incorporate that into my formula?

  • jcarville's avatar
    jcarville
    Skilled Sharer
    8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        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