Forum Discussion
Tracking Unsold 90 Day Goal
I am creating sales goal reports for my company's sales managers, and one of the types of goals their team is assigned is called a New Point of Distribution Goal or a 90 Day Unsold POD Goal. This involves a sales rep selling an item to an account that has not bought that item in the past 90 days. I have an Invoiced Sales Table (photo below) with the data. Now I need a measure that will compute the PODs that I can filter down further for visuals.
What I seem to be trying to write out in DAX is the following looking at each invoice line: Has (CustomerID) bought (ProductID using the CESold greater than 0) in the 90 days prior to the (InvoiceDate). If yes, then return 0. If no, then return 1.
I'm also not 100% certain if this can written out as a SUMX measure of the results then filtered down as needed, or if I need to create a calculated column in the Invoiced Sales Table to evaluate each row of the invoiced sales table then create a measure of Total 90 Day Unsold PODs which I would then filter in the visuals.
2 Replies
- parry2k
Super User
per771 I would recommend to put sample data in a pbix file with expected output and share it thru one drive/google drive, it will be easier to provide the solution. Remove sensitive information before sharing.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thanks for responding parry2k . I am not allowed contractually to really share any information, even scrubbed. I feel like this should be a lot easier than I am making it. I've played around with IF, but I don't understand how to evaluate the one row against the others in the Invoiced Sales table to establish if there were sales in the past three months of that item to that account.
I am just leaning towards doing a new calculated column because it makes more sense to me. I don't think a measure could do this, but I may be wrong. I figured I could then do a measure to get the sum of the 1 results.
TLDR I need to figure the following out. I can write it out, but I don't know how to make the DAX work.
1. Is the case equivalent sold (CESold) greater than 0? (if yes then move on to evaluate number 2. If no, the result is automatically 0)
2. If yes to above, has the product (ProductID) been sold to the account (CustomerID) in the 90 days prior to the Invoiced Date (InvoiceDate).
3. If all of the above is True, then return a 1 (this would equal the sales rep establishing a new Point of Distribution). If any of the above is false, return a 0 (this would equal a sale, but not a new Point of Distribution).
Here is the one line from the Invoiced Sales table again that I use as my FACT table.
