New measure - rolling 30 days of created opportunities

[Tschwenn]

I'm looking to capture a new measure reflecting Revenue of New Opportunities created over a rolling 30 day period. I know that there is a Relative Date Filtering option in the Visual Filters - however, I need to capture this as a Measure in order to use it in a different formula.

Here is the formula I currently have:

NEW OPPS = CALCULATE(SUM('Opportunity Splits'[Projected Annualized Rev CONVERTED],DATEADD('Business Unit'[CreatedDate].[Day],30,DAY))

Which returns an error for "Too many arguments were passed to the SUM function. The maximum argument count for the function is 1.

[v-lid-msft]

Hi @Tschwenn ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

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

[v-lid-msft]

Hi @Tschwenn ,

This  error mainly caused by the incorrect place of parentheses. You can try the following formula.

NEW OPPS =
CALCULATE (
    SUM ( 'Opportunity Splits'[Projected Annualized Rev CONVERTED] ),
    DATEADD ( 'Business Unit'[CreatedDate], 30, DAY )
)

Best regards,

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

[Tschwenn]

Thank you for the tips.

I believe I'm close to the desired solutions. However, I'm still running into a couple issues:

1. I used the following formula. However, I receive an error message about duplicate date returns [see screenshot].

NEW OPPS = CALCULATE
(SUM
('Opportunity Splits'[Projected Annualized Rev CONVERTED]),
DATEADD('Opportunity Splits'[CreatedDate].[Day],30,DAY)
)

The expected result should show a revenue figure of all the Opportunity Splits CREATED within the last 30 days.

2. I used the following formula for the 2nd inquiry. I receive the following error message: Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

RECENT DECISIONS - WON =
CALCULATE(
SUM('Opportunity Splits'[Projected Annualized Rev CONVERTED]),
FILTER('Opportunity Splits'[Split Stage (Pipeline-W-L-Other)]="2. WON"),
DATESBETWEEN ('Opportunity Splits'[CreatedDate], TODAY () - 30, TODAY ())
)

The expected result should show a revenue figure of all the Opportunity Splits WON within the last 30 days

[v-lid-msft]

Hi @Tschwenn ,

For the first issue, To use Time intelligence function, Continuous time is necessary for most time. you can try to generate a calendar table , create relation and using date column in your DAX.

For the second issue, I am sorry for miss the table in the DAX formula. you can modify to the following, if has error as the first issue, create the calender table and change the date column in the formula.

RECENT DECISIONS - WON =
CALCULATE (
    SUM ( 'Opportunity Splits'[Projected Annualized Rev CONVERTED] ),
    FILTER (
        'Opportunity Splits',
        'Opportunity Splits'[Split Stage (Pipeline-W-L-Other)] = "2. WON"
    ),
    DATESBETWEEN ( 'Opportunity Splits'[CreatedDate], TODAY () - 30, TODAY () )
)

Best regards,

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

[Tschwenn]

In addition, I would like to capture ONLY the "WON" revenue value over the last 30 days - by amending the following formula:

RECENT DECISIONS - WON =
CALCULATE(SUM('Opportunity Splits'[Projected Annualized Rev CONVERTED]),'Opportunity Splits'[Split Stage (Pipeline-W-L-Other)]="2. WON")

[v-lid-msft]

Hi @Tschwenn ,

We can try to using following measure to meet your second requirement.

RECENT DECISIONS - WON =
CALCULATE (
    SUM ( 'Opportunity Splits'[Projected Annualized Rev CONVERTED] ),
    FILTER ( 'Opportunity Splits', [Split Stage (Pipeline-W-L-Other)] = "2. WON" ),
    DATESBETWEEN ( 'Business Unit'[CreatedDate], TODAY () - 30, TODAY () )
)

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

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