OK, that helps. I may need a bit of time to get to this, but if no one else jumps in, I will definitely have a look this evening.
thanks for your time, i reconfirmed with the client and they want the previous value to be; the amount of days interval(the date diff in days between from and to date) to be subtracted from the Fromdate
datediff: 10 days
Prev From: 12/31/2017 Prev To - [Diff (To - From)]
Prev To: 1/9/2018 [From - 1 day]
Prev From--------- 10Days -----------Prev To | FROM--------- 10days ----------TO
OK, one more question. The data set has a lot of different date fields. Which one should be used for the comparison to your start and end dates?
you can use any of the date field, Todate will most likely be termination date.
Sorry for the delay on this. I'm not sure how much you've done already, so I sort of start over by creating two parameters Date Start and Date End to be used as the drivers for this. I then created a few calculated fields:
// Get the number of days in the selected period.
DATEDIFF('day',[Date Start], [Date End])
In Current Period?
// Is this record within the start and end date parameters?
IF [Date Terminated]>=[Date Start] AND [Date Terminated]<=[Date End] THEN
In Previous Period?
// Is this record within the previous period??
IF [Date Terminated]>=DATEADD('day',-[Period Length],[Date Start]) AND [Date Terminated]<[Date Start] THEN
Time to Fill Current
// Get the time to fill only if it's in the current period.
IF [In Current Period?]="Y" THEN
Time to Fill Previous
// Get the time to fill only if it's in the previous period.
IF [In Previous Period?]="Y" THEN
Then you can build out your view however you want, but as an example, drag Measure Names to the rows shelf, then drag Measure Names to the filters and only select Time to Fill Current and Time to Fill Previous. Next remove the AVG(Time to Fill) pill from the text card and drag over Measure Values. It should now show both the current and previous time to fill as shown below:
You'd then need to do similar calculated fields for all of your other KPIs using whichever dates were applicable for each. Workbook is attached.
Time to Fill.twbx 121.5 KB
Thank you, i had gone as far as creating in In previous period value before seeing this, it helped alot.
Great. If this answers your question, would you be so kind as to mark my response as the "correct answer" so we can close this thread and so that others can find the answer to similar questions in the future? Thanks!