Calculating Days Between Different Stages of a Process

[24601]

Hi,

I would like to calculate the number of days an item is taking to go through different stages of a sale

Item No.              Stage                             Date

123                     Stock Aquired                01/01/2021

123                     Placed On Sale              06/01/2021

123                     Purchased                     16/01/2021

123                     Shipped                         20/01/2021

123                     Received by Customer  27/01/2021

For example Item 123, the number of days between

Stock Acquired – Received by Customer

Placed on Sale - Purchased

Shipped – Received by Customer

I can set up individual queries for each of the above.

I want to apply this to all items, there will be multiple items, and the list will involve 10,000+ entries with refreshes required frequently.

I’ve tried searching forums but only found ways of calculating previous dates. Perhaps I’ve got my terminology and phrasing wrong so apologies if the answer is already here.

I would really appreciate any help you can give me. Thanks!

[24601]

@askhanduja Thanks, it's not quite what I was looking for but thanks very much for the effort!

@jis This is really close to what I'm looking for, thanks very much! Is there a way to rollup the KeyMeasures to see them overall as well as by item?

[24601]

@Jihwan_Kim 

I believe I incorrectly tagged someone else when refering to your previous reply. 

Your solution is very close to what I am looking for. I can get the days between stages on an individual item level. However, I am looking to show rolled up monthly, quarterly and yearly views. 

Do you know of any way how to achieve this? I have tried to use your measure to achieve this but can't figure it out.

Any further help would be massively appreciated. 

[askhanduja]

Hi,

It seems from your question that you would like to see a report that looks something like the following:

I would recommend, that you build a model with a start schema rather than a single table model. See an example picture below of how you might model it.

With a model such as the one above, you can then write a simple measure like the following to display the report shown in the first image.

Days between stages = 
VAR __StartStageDate =
CALCULATE(
    SELECTEDVALUE(factItemStages[StageDate]),
    REMOVEFILTERS(dimEndStages)
)
VAR __EndStageDate =
CALCULATE(
    SELECTEDVALUE(factItemStages[StageDate]),
    REMOVEFILTERS(dimStartStages)
)
VAR __Result = 
// Use following version if it is required that an end stage should only be populated by those values that are **usually expected** to be after a start stage. I wouldn't recommend this approach, but just in case this is what is required
// start stage 
// IF(
//     SELECTEDVALUE(dimEndStages[EndStageCode]) > SELECTEDVALUE(dimStartStages[StartStageCode]),    
//     INT(__EndStageDate -__StartStageDate)
// )

// Use this version if all combinations of start stage and end stage are acceptable.
INT(__EndStageDate -__StartStageDate)

RETURN
__Result

I am linking the power bi file with the above model and measure here.

------------------------------------------------------------------------------------------------------------

If the above answer solves your issue please accept it as the solution so others with the same issue can also find it. Also, a thumbs up would be great!!

[Jihwan_Kim]

Hi, please check the below picture and the sample pbix file's link down below.

Link to the sample pbix file