Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago

Measure requires additional parameter

Hi,

 

This measure successfully gives me the amount of ALL Cases closed:

 

YTD Closures = CALCULATE(COUNT('Cases'[incidentid]),FILTER('Cases',[statecode_display]="resolved"))
 
I would like to modify it so that it just counts YTD or after 31/03 using the 'resolution Date' column
 
I'm not quite sure how to modify the measure?
Thanks
Need Help

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi ArchStanton ,

     

    If you get yourself set up with a related calendar table, you can make use of the OTB time intelligence functions.

    There's plenty of resource online how to create a calendar table, so I'll assume you've created one with a column called [date] and related it to your fact table in the data model on calendar[date] ONE : MANY Cases[Resolution Date]:

    _closuresYTD =
CALCULATE(
    COUNT('Cases'[incidentid]),
    [statecode_display] = "resolved",
    DATESYTD(calendar[date], "31/03")
)

     

    Pete

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      Hi Pete,

       

      Thanks for replying.

      Hi have a date table but the dates I need to reference are are in the cases table where the resolution date exists. There are several date columns in my Cases table and I need to perform calculations or measures on them directly if possible?

       

      So going back to my original example, I have resolution date in my Cases table, how would you modify the code for YTD so far?

       

      Thanks

      A

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

        Hi ArchStanton ,

         

        Relate your calendar[date] field in your data model to Cases[Resolution Date]. If you already have a relationship in place, this new one will show as INACTIVE when you create it. To activate the inactive relationship for the purposes of your measure, use this:

        _closuresYTD =
CALCULATE(
    COUNT('Cases'[incidentid]),
    DATESYTD(calendar[date], "31/03"),
    USERELATIONSHIP(calendar[date], Cases[Resolution Date])
)

         

        I've removed the [statecode_display] = "resolved" condition as I'm assuming that items only get a [Resolved Date] once marked as resolved.

         

        Pete