topic Re: Count IF Statement in DAX Commands and Tips

Count IF Statement

Anonymous — Wed, 29 Mar 2023 19:59:34 GMT

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 they have purchased in the last 6 months.

I am looking to track new orders which will be coming in next month - where there is currently no distriubtion.

So for example: If no distriubtion for this product, then count this order in April.

Ultimately, it would be great to also track the new orders through conditional formatting (in a different color).

Any help would be so appreciated.

Thank you so much,

Christina

Re: Count IF Statement

v-jingzhang — Fri, 31 Mar 2023 02:24:28 GMT

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

Re: Count IF Statement

Anonymous — Fri, 31 Mar 2023 13:09:50 GMT

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