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

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago

I tweaked the code to this as it wasn't working initially:

 

YTD Closures = CALCULATE
            (COUNT('Cases'[incidentid]),
            DATESYTD('Date'[Date], "31/03"),
            USERELATIONSHIP('IntoArea'[Date], 'Cases'[pre_resolutiondate]),
            FILTER('Cases','Cases'[statecode_display] = "resolved"))

 

However the numbers are way off, I'm gettin 1,945 and I should be getting 3,426

 

Is it possibe to do away with SYSDATE and just possibly use calculate, countrows or count & format combination to count how many cases have been resolved since resolution date >"31/03") this year?

It sounds so easy but it's proving to be very problematic!

 

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi ArchStanton ,

     

    If you're getting the wrong numbers using the measure I provided then I think there must have been a setup issue. Please check the following:

     

    1) You must use calendar[date] on your chart axis

    2) Your calendar table must be marked as a date table

     

    3) There must be an active (if NOT using USERELATIONSHIP) or an inactive (if using USEREL) relationship between your fact table and the calendar table.

     

    Getting the correct setup to be able to use the time intel functions is the optimal solution, so please check the above first.

     

    You can run the cumulative value without using the DATESYTD() time intel function, but you'll still need to reference the calendar table and use calendar[date] on the chart axis, something like this:

    YTD Closures =
VAR __cDate = MAX(calendar[date])
VAR __cYear = YEAR(__cDate)
RETURN
CALCULATE(
    COUNT(Cases[incidentid]),
    FILTER(
        ALLSELECTED(calendar),
        calendar[date] <= __cDate
        && calendar[date] >= DATE(__cYear, 04, 01)
    )
)

     

    If this still doesn't work, then I'll need a bit more information about your data model (relationships and fields), and some sample data to work out what's going wrong in your scenario.

     

    Pete

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      Thanks Pete, I will explore these ideas over the coming weeks and get back to you if I have any questions.