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

Question

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!

Answer 1

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.