Forum Replies Created
-
Posts
-
For now we are only looking at a single month but the hope is that we can look at more months as we get more adept at tracking this data.
For the training date, the reps do not train in each pod, only the first 45 days from First training date is considered training. After that, switching pods is just a matter of pod membership. If, after 40 days of training JBEIBER switches to another pod, only 5 more days of training will occur before becoming a full pod member.
And, just to keep it interesting, the monthly calculations will be cumulative, adding up from day 1 of the month so that each pod knows the current standings. Which, with Power Query, this is simply a matter of updating the tables to pull in additional rows. I just need to be able to update it on a daily basis through refresh.
Power Query was a fantastic tool for this. I had fallen into the very common trap of thinking of Power Query only as a means to upload data into PowerPivot. since having this discussion, I have successfully completed this task and trained a coworker on using PowerQuery for this type of work. I have succeeded in saving her hours per day and reduced her workload by nearly a full day per week.
Thank you so very much for your help!
Very elegant solution. I will give it a go tomorrow when the pressure of Monday is no longer leaning on my shoulder. Thank you, once again, for giving your time here to help all of us out.
I will take option B. We only allow one order date to be associated with one sales order and I am asking about, for a single customer, the average amount of days between their orders.
What I have so far is two created columns:
Date First Purchase = FIRSTDATE(SalesPerformance[OrderDate])
Last Order date = LASTDATE(SalesPerformance[OrderDate])
I am assuming that I can do a simple subtraction to get the difference in days. My concern is for customers without a second order. I will be using product categories on some of my tables to determine repeat buyers for certain products and the average amount of days between orders. How would I account for first time buyers who just purchased? And, is there a better way to do this without creating columns?
Each distinct Sales Order number counts as a purchase with the order date tied to that number whereas multiple invoice numbers and ‘posting dates’ can be associated with a sales order number (aka purchase). Each line on the table is attributed to a line on a sales order.
My main concern for this calculation is to find customers with multiple sales order numbers and to determine the average number of days between the order date associated with that sales order number. Although, it would be nice to also figure out the number of days since ‘last purchase’ (or only purchase) so that it could be used to determine ‘lost customers.’
Your help is always appreciated as I learn something new every time we converse. In fact, my last question completely revolutionized how I use PowerQuery.
I would like to keep all MPN’s. Wondering if I can create a combined MPN table pulling from both sources and removing duplicates in order to bridge them together.
Yes, I would like to keep the records that are listing under blank. Currently around 400 of the records are being lumped into the blank listing.
As I said, the data seems to be a mess. Every [MPN] has a [Code] on the item table and the MPN table but not every [Code] has an MPN. The [Item #] on the Bid table is an MPN but not ever [Item #] MPN is found on this Item Table or the MPN table as we may not carry every item. The duplicates on the item table occur both as redundant as well as some having slight variations. The duplicates on the Bid table are frustrating as the MPN is exactly the same but the item description is different as in one MPN might be both for a door lock and for a key but both have different assumed prices and neither are specified on the item table.
The ID from the Bid table is simply a line marker to assist the team with locating the item on the bid. Its usefulness is limited to formatting alone.
My MPN table was created in PowerQuery by pulling from the Item table the Code, desc, and MPN to be used as a bridge table between the two.
I have expanded the MPN to include Code, desc, and MPN and it is working better today. Now I am able to have MPN, Code, and desc as my rows and pull in the necessary quantities and prices I just cannot figure out the best way to handle those falling under blank. The problem is, the quantity is aggregating under ‘blank’ when the item doesn’t exist in the item table and it is not successfully displaying the Item# for those blanks.
Final shot
For some reason I cannot add more than one image.
You are absolutely correct! I am happy to know this was a simple fix. Thank you very much, Tom, for giving so much of your time here in the forum to help those of us still learning the nuances of DAX.
Kristi
As an update: I still have not heard from Microsoft on this issue. I still have my data model and it still functions with static data. I cannot refresh as it says I have zero queries in Power Query but in PowerPivot it tells me I cannot access those tables due to them being created in Power Query.
I cannot work with the connections as they ‘exist’ in PowerQuery which says that they do not exist.
If any Microsoft folks hang around in here maybe they can dig up my ticket . . .
I am using 2013 and haven’t had an issue of my PowerPivot Tab disappearing in several months. My data model is also still intact and functioning. I simply cannot refresh queries and the PowerQuery pane shows 0 queries.
The only error I am getting is a random Linguistic Schema failed to update error which I have not been able to pin down. It comes in the PowerPivot Window when refreshing data.
While this is frustrating, the DAX I use is used elsewhere in other reports. I can co-opt a different report and change it to suit my needs. Was just hoping to find out what caused this to prevent it from occurring again in the future to a report less adaptable to being rebuilt.
Thank you for the information on extracting DAX. I will file that away for future use.
That was the problem. Update resolved the issue. Thank you for pointing me in that direction!
I am using PowerPivot in Excel 2013. I am referring to the indicator flags under icon sets in conditional formatting. They initially apply (with the show icon only box checked). As soon as the table expands or collapses, however, the number hidden under the flag is displayed and the flags never come back.
