Measure requires additional parameter

[ArchStanton]

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

[BA_Pete]

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]

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]

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]

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]

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]

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