Forum Discussion
Track open work order totals
I have "Table A" that is similar to this: Work Order#, Quote Amount, Start Date, End Date A work order is in open status when it is started and not ended.
I have a date dimensions table that I want to link.
I want to track the total "Quote Amount" that we have in the shop over time. So if a work order has a quote amount of $1,000 and had a Start Date of 7/01/2016 and an End Date of 7/21/2016, I want to show that $1,000 for that time frame but then drop off once is ended.
We are tracking our WIP(work in process) amount and want to see changes in WIP over time. Currently, I know our total WIP right now.........Total "Quote Amount" that is in open status today. I want to take the next step and track it over time.
So, I believe I need to link one of the dates in Table A to the date dimensions table. I'm just not sure which date, Start or End, and then what to do after that. I'm creating a Cluster column chart and put "Quote Amount" as the value and put the "Date" from the date dimension table as the Axis but I have the Start Date linked to the date dimension table so it is just telling me the "Quote Amount" that was Started on each day, not the total I had open during that day.
Thank for the help.
9 Replies
samdthompsonMemorable Member
Hi, i think you want to put in a calculated column for an open / closed flag. this will give you the ability to easily filter in your calculation.
you can then visulaise say by start date how many orders are open and what the value of those are ditto the closed ones.
// if this is a solution please mark as such
Anonymous
Check if the link helps. This is some thing similar to what you want but the measure is different.
http://community.powerbi.com/t5/Desktop/Cohort-Analysis/m-p/10206#M2084
If it helps please give KUDOS.
Cheers
CheenuSing
- v-huizhn-msft
Microsoft Employee
Hi Anonymous
I am trying to reproduce your scenario using the following sample data and get the expected results.
There is not relationship between the tables.
First, I add a calculated column: Open = IF((Table2[Start Date]<LASTDATE(Table1[Date]))&&(Table2[End Date]>=LASTDATE(Table1[Date])),"True","Faluse"). The new columns display a work order is still open.
Then I create the below measure which calculates the total of Quote Amount where the work order is still open.
Total of Amount = CALCULATE(SUM(Table2[Quote Amount]),FILTER(Table2,Table2[Open]="True"))>>I believe I need to link one of the dates in Table A to the date dimensions table. I'm just not sure which date, Start or End, and then what to do after that.
You need to verify the relationship between based on your business, and link the table. If you link table by start date, which means very day you have a work order and the day is the start day of work order? It seems nonsensical.
It will category in very item in Axis if you want to create Cluster column chart. After understand your table relationship and what result you want, then you can create chart you want. More details, please review this article.
Best Regards,
AngeliaI'm trying to find a way to post my sample pbix file here so it can be reviewed. I've got a message into the moderator so I'll wait for their reply.
I'm not sure why I would need to find out if the work order is currently open. I'm trying to track the value over time so while there is multiple work orders that are closed now, they were open over the past years and I need to track that also.
Plus, I can't figure out this: Open = IF((Table2[Start Date]<LASTDATE(Table1[Date]))&&(Table2[End Date]>=LASTDATE(Table1[Date])),"True","Faluse") The formula is not allowing me access to the different table while I am "Adding Custom Column".
- v-huizhn-msft
Anonymous The column "Open" added just used to acheive the garget clearly, it not required. The formula is not allowing me access to the different table while I am "Adding Custom Column". Could you share the error message for further analysis?