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!
@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?
@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.
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!!
Hi, please check the below picture and the sample pbix file's link down below.
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.