Filter a measure by dates in last month

[Anonymous]

Hello,

I am attempting to create a measure that will count the [Topic] of opportunties that were created in Dynamics CRM, that do not have a [Status] of "Open" in the last calendar month. As it is currently March, I want a count of February.

It is EASY putting this all in as a count of values and applying a visual level filter... BUT this needs to be a measure so I can use it in the calculation of another measure. 

Here is what I have come up with so far but am getting an error that my "Created On" column has duplicate values. 

# of Ops Opened Last Month = CALCULATE(count(opportunities[Topic]),filter(opportunities,[status]<>"Open"),DATESINPERIOD(opportunities[Created On],TODAY(),-1,MONTH))

The date part of my measure is where I think I am having the issue...

Does anyone have any pointers?

Thank you in advance for your help. I appreciate it. 

[jtownsend21 · Accepted solution]

Perhaps try a datediff:

DATEDIFF(
    TODAY(),
    'Date'[CreatedOn]
    ,MONTH) = -1

I've had trouble with the datesinperiod before.

Here is what I am suggesting for your full formula: 

# of Ops Opened Last Month = 
    CALCULATE(
        COUNT(opportunities[Topic]),
        FILTER(opportunities,[status]<>"Open"),
        DATEDIFF(
            TODAY(),
            'Date'[CreatedOn],
            Month
        ) = -1
    )
    

[ScottTynan]

@jtownsend21 Thank You! So much, I have been bashing my head in over this for the last 3 hours. 
Any idea why the DATESINPERIOD function refuses to work?

[Kedow]

Awesome, really helped me with my last calendar month issue

[Anonymous]

That worked! Thank you for your help.

[jtownsend21]

Happy to help.