Dynamic Filter

[StoryofData]

Is there a way to create a dynamic measure that will calculate # of cases in a year as the years go by?

I am doing several calculations off of this measure, do I have to create one each new year?

Cases Opened 2023 =

CALCULATE([Cases Opened], 'Calendar'[Fiscal Year] IN { 2023 },

USERELATIONSHIP('Calendar'[Calendar Month Name Full],'Ref Sort Order'[Month]))

And, how do I limit the measure to show data for completed months only? 
For example, if we are in April of 2023, then the measure will limit data to March of 2023 since that month is already done. 

[Anonymous]

Hi @StoryofData ,

Please try this measure.

Measure = 
VAR _today =
    TODAY ()
VAR _max_date =
    EOMONTH ( _today, -1 ) + 1
VAR _min_date =
    EOMONTH ( _today, -2 ) + 1
VAR _result =
    CALCULATE (
        [Cases Opened],
        'Calendar'[Date] >= _min_date
            && 'Calendar'[Date] < _max_date,
        USERELATIONSHIP ( 'Calendar'[Calendar Month Name Full], 'Ref Sort Order'[Month] )
    )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amitchandak]

@StoryofData , In the attached file after signature, refer the prior period measure