Month To Date Measure using date of last transaction

[reimero]

Hi all. I'm creating a KPI dashboard for my department. One of the KPI's we measure is % customer cases closed by month, quarter year, etc. I've created the measures and they work when i choose the appropriate year and month date slicers. However i'd like to have it calculate based on the date of the most recent transaction/case entered in our system. I.e if the most recent customer case is March 10th, calculate % MTD. The idea being this is can float from month to month without me having to click the date slicers each time and i can then share these KPI's with my sr. management team without them needing to do any additional clicking.

My current measure which works with date slicers:
% Closed MTD = TOTALMTD([% Tickets Closed],'Calendar'[Date])

For what I want to do i assume i'll need some type of FILTER which looks into my 'incidents' table determines the date of the last customer case that was logged into our system, then reverse calculates the first date and last date of the month). But I'm struggling with where to start... as i'm fairly new to DAX, but hungry to learn. Don't need final solution but if i can be pointed in which direction to go...


Thank you

[reimero]

I was able to get the below measure to work for me. If someone knows of a more efficient way to do this calculation i'm all ears. But it got me the results i needed.

% Closed MTD Unfiltered = CALCULATE([% Tickets Closed],DATESBETWEEN('Calendar'[Date],STARTOFMONTH(LASTDATE(Incidents[Date Occured])),ENDOFMONTH(LASTDATE('Incidents'[Date Occured]))))

[Owen Auger]

Hi reimero,

There are multiple ways of handling this.

If you still have your original measure [% Closed MTD], then just write:

% Closed MTD Unfiltered =
CALCULATE ( [% Closed MTD], Incidents )

otherwise:

% Closed MTD Unfiltered =
CALCULATE ( TOTALMTD ( [% Tickets Closed], 'Calendar'[Date] ), Incidents )

Including Incidents as a filter argument actually includes the extended Incidents table, which includes related rows of Calendar. This effectively limits the context to dates present in Incidents, and the latest date will determine the date at which 'MTD' is defined.

Note, any of these measures are still subject to filters on Calendar or other columns, i.e. the "last date" is the last date in Incidents in the current filter context.

Cheers,
Owen