Forum Discussion

StoryofData's avatar
StoryofData
Helper III
3 years ago

Dynamic Filter

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. 
Need Help
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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