Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Counting frequency for different cycle

Hi everyone, 

 

I would like to create a DAX column that can count the occurrences of a product category that must be put on shelf according to a specific cycle. For example, some category needs to be put on shelf only once per week (weekly). This mean that orders need to be placed consecutively to follow this rule. However, there may be some cases that we cannot follow this rule, so that's why I would like to create this column for checking. 

Let me give you more information about the example. Here we have the list of orders. For this case, we will concern only the frequency of category that the cycle is not daily (set the frequency to 1, if it is daily). We can see that food category are put on shelf for 2 times in week 32. Therefore, the later order will have the frequency of 2. For stationary category, which is monthly cycle, the orders are consecutive, so they will have the frequency of 1. Also, the food category is put on shelf again consecutively but on different weeks, so the frequency of the later one will be reset to 1.

 

What I was thinking is to use RANKX function, but I am not able to do it.


Your help is much appreciated.

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Anonymous 
    When you mean consecutive, do you mean the category is same as the category in the previous row?

     

    This seems like a multiple if question, but dealing with consecutive using dax is complicate. Please share a table that I can copy paste, so we can test out.

     

     

    Best

    Paul