cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
24601
Frequent Visitor

Calculating Days Between Different Stages of a Process

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!

4 REPLIES 4
24601
Frequent Visitor

@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
Frequent Visitor

@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
Helper I
Helper I

Hi,

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

Report.jpg

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.

Model.jpg

 

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
Super User
Super User

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

 

 

Picture3.png

 

 

Link to the sample pbix file 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors