Tuesday, July 10, 2012 10:34 PM
I have 2 tables linked on date. One table is the typical dates table providing presentations of dates (like day, weeknr, quarter nr, year nr etc).
The other table is a table containing details of support requests where you have a.o. requestNr, Open date, Solve date, Close date and Due date of the request.
The link between the tables is on Open Date.
What I am trying to accomplish is a list of snap shots in time (end of each week) where I want to know how many of the requests at that point of time were in status Opened, Solved or Closed and if they were still in time (before the due date). I think I should do that by comparing the listed dates to LASTDATE(DatesTable[Date]). As rowlabel I use Calendarweek, which should summarize my results per week (or could be month). When I create a measurement with just
and put that in my Values, I indeed see the correct ending date for each week in my pivot table rows.
Now, somehow I need to compare that LASTDATE with the column DueDate, to see if the request passed the DueDate in that week. But I don't understand how to do that. I tried
COUNTX to count the RequestNrs (should give amount of requests)
Filter to create the base of data
ALL(Requests) to get rid of the filter limiting to the requests that are opened in that period
[DueDate]<LASTDATE(DatesTable[Date] to limit to all requests that have a DueDate smaller than the last date in that week
But this actually results in counting all entries where the DueDate is empty. Even while I have some entries in there that should match the criteria in certain weeks.
When I change the formula into something like [DueDate]<DATEVALUE("2012-06-01"), I DO get the count of entries where the DueDate is before that date.
Where do i go wrong?
Don't know how to provide the example data. When desired I can mail the workbook with example data.
Thursday, July 12, 2012 12:18 PM
as your facts are related using OpenDate, your fact-table is automatically filtered to requests that have been opened in the selected week
if you further want to filter out the rows that have a DueDate < LastDate of the the selected week, you can try the following:
Try3:=CALCULATE(COUNTROWS(Requests), FILTER(Requests, Requests[DueDate]<LASTDATE(DatesTable[Date])))
though i am not quite sure about your exact requirement :)
- www.pmOne.com -
Friday, July 13, 2012 5:46 PM
First of all, thank you for replying.
I want to create a status report that reconstructs the status at certain points in time. For this question I will take end of each week.
Let's take end of week 1 and end of week 2 as two points in time.
On the row of week 1, I want to see the count of all tickets that still did not pass their due date at the end of week 1 (not limited to tickets that were opened in week 1).
On the row of week 2, I want to see the count of all tickets that still did not pass their due date at the end of week 2 (not limited to tickets that were opened in week 2, also the ones that were opened in e.g. week 1).
So, if a ticket has a due date that is somewhere in week 2, it should be counted as not-passed-due-date in the row of week 1, since at that point of time it did not pass the due date yet, while in the row of week 2 it should be counted as did-pass-due-date, since at the end of week 2 the due date was passed.
In these counts I the only filter I want to have on OpenDate is that it should have been opened before the end of the week in that row. So, in the row of week 2, I should include all tickets opened in week 2 or before, but not the ones that were opened after the end of week 2.
I hope that this makes my requirement clearer.
At the end I want to accomplish something like:
if Opendate > LASTDATE
then ticket not considered in this count
else if CloseDate < LASTDATE
then ticket is closed
else if SolveDate < LASTDATE
then ticket is solved
else if DueDate < LASTDATE
then ticket is still open and is overdue
else ticket is still open an is still in time
Closed overdue and Solved overdue can be calculated in a another iteration once I have this one working.
Wednesday, July 18, 2012 9:07 AM
this one should work for OpenOrders (your last else-branch):
CntOpenOrders:=CALCULATE(COUNTROWS(Requests); FILTER(ALL(Requests[OrderDate]; Requests[DueDate]); Requests[OrderDate] < LASTDATE('Date'[Date]) && Requests[DueDate] > LASTDATE('Date'[Date])))
and for OverdueOrders:
CntOverdueOrders:=CALCULATE(COUNTROWS(Requests); FILTER(ALL(Requests[OrderDate]; Requests[DueDate]); Requests[OrderDate] < LASTDATE('Date'[Date]) && Requests[DueDate] < LASTDATE('Date'[Date])))
please note that i did not define any relationship between Requests and Date-Table
- www.pmOne.com -
- Proposed As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Saturday, July 21, 2012 5:56 AM