Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Count IF Statement

Hi!    I'm working on building a sales distribution gap report.     I've built a matrix where I can see where customers have not purchased specific products and counted the orders on products the...
v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi Anonymous 

 

Sorry I don't understand your expected result. The next month hasn't come, how do we know which products will have orders in the next month? And for the example "If no distriubtion for this product, then count this order in April", do you mean one product has not been purchased by a customer in the last 6 months? If it will be purchased by a customer in the next month, how do you want to display the expected result? In the current matrix visual or in another visual? If in another visual, how should it be like?

 

Best Regards,
Community Support Team _ Jing

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi v-jingzhang ,

     

    Thanks for the response!  Makes sense - Let's use the example of this month.

    In the table below I have my products, Customers, "Yes or No" of customers who had distribution between Oct and Feb, count of sales orders the customer had between Oct and Feb for a specific product, and count of sales orders the customer had this month for a specific product

     

    In this example, we can see some customers that had ordered in the last 5 months, didn't order this month for specific products.

     

    Sales Order Count Oct22-Feb23 Measure:

     

     

    Sales Order Count Oct22-Feb23 = 
 CALCULATE(COUNT('POSTED DOCUMENTS'[Document No]),DATESBETWEEN('POSTED DOCUMENTS'[Posting Date], DATE(2022,1,10),DATE(2023,28,2)))+0

     

     

     

    March Sales Order Count measure:

     

     

    March Sales Order Count = 
 CALCULATE(COUNT('POSTED DOCUMENTS'[Document No]),DATESBETWEEN('POSTED DOCUMENTS'[Posting Date], DATE(2023,1,3),DATE(2023,31,3)))+0

     

     

     

    You'll see some customers didn't order this month, but they did have an order over the course of the past 5 months.  And we also see in this data that there were not any distribution gaps filled.  Every sale in March also had a sale in the last 5 months.  

     

    To link back to the matrix - I'm looking to create a measure where if a customer were to have an order in March, but never ordered between Oct thru Feb - that the Matrix would highlight the new sales order since we are filling a distribution gap.  

     

    Ideally, I also want to be able to track the March sales orders that come in where we didn't have an order between the Oct - Feb time frame.

     

     

    Thank you!! 

    Christina