Creating a DAX pattern that counts days between a date field and a month value on a chart's x-axis

0
votes

I am struggling with a DAX pattern to allow me to plot an average duration value on a chart.

Here is the problem: My dataset has a field called dtOpened which is a date value describing when something started, and I want to be able to calculate the duration in days since that date. I then want to be able to create an average duration since that date over a time period. It is very easy to do when thinking about the value as it is now, but I want to be able to show a chart that describes what that average value would have been over various time periods on the x-axis (month/quarter/year).

The problem that I am facing is that if I create a calculated column to find the current age (NOW() - [dtOpened]), then it always uses the NOW() function - which is no use for historic time spans. Maybe I need a Measure for this, rather than a calculated column, but I cannot work out how to do it.

I have thought about using LASTDATE (rather than NOW) to work out what the last date would be in the filter context of any single month/quarter/year, but if the current month is only half way through, then it would probably need to consider today's date as the value from which to subtract the dtOpened value.

I would appreciate any help or pointers that you can give me!

2
powerpivotdaxexcel-chartstime-measurement
Hi Geoff - I think you need to clarify in your question how you would want those averages broken out over time. Taking one thing in isolation ... if the thing was started in February but ran through March, April & May, then in what month would you want that thing's average duration represented in your chart? – Rory
Thanks Rory. To add a little more detail, this dataset involves cases that have unknown durations. I am interested in cases that have not yet been marked as closed. In your above example, if there was a case that opened in Feb and closed in May, then I'd expect that 'Case Age' measure would be null in May (as the case is closed). In Feb, March and April, I'd expect the value to be about 30 days more than the previous one. The actual value should be the Case Age value as it was on the last date of that month. On a chart, the y-axis would be Case Age and the x-axis would be month. Geoff. – Geoff

2 Answers

0
votes

It looks like you have a table (let's call it Cases) storing your cases with one record per case with fields like the following: casename, dtOpened, OpenClosedFlag

You should create a date table with on record per day spanning your date range. The date table will have a month ending date field identifying the last day of the month (same for quarter & year). But this will be a disconnected date table. Don't create a relationship between the Date on the Date table and your case open date.

Then use iterative averagex to average the date differences.

Average Duration (days) :=
CALCULATE (
    AVERAGEX ( Cases, MAX ( DateTable[Month Ending] ) - Cases[dtopened] ),
    FILTER ( Cases, Cases[OpenClosedFlag] = "Open" ),
    FILTER ( Cases, Cases[dtopened] <= MAX ( DateTable[Month Ending] ) )
)

Once you plot the measure against your Month you should see the average values represented correctly. You can do something similar for quarter & year.

0
votes

You're a genius, Rory; Thanks.

In my example, I had a dtClosed field rather than an Opened/Closed flag, so there was one extra piece of filtering to do to test if the Case was closed at that point in time. So my measure ended up looking like this:

Average Duration:=CALCULATE(
        AVERAGEX(CasesOnly, MAX(DT[LastDateM]) - CasesOnly[Owner Opened dtOnly]),
        FILTER(CasesOnly, OR(ISBLANK(CasesOnly[Owner Resolution dtOnly]), 
                             CasesOnly[Owner Resolution dtOnly] > MAX(DT[LastDateM]))),
        FILTER(CasesOnly, CasesOnly[Owner Opened dtOnly] <= MAX(DT[LastDateM]))
    )

And to get the chart, I plotted the DT[Date] field on the x-axis. Thanks very much again.