Forum Discussion

jmateu88's avatar
jmateu88
Helper I
3 years ago
Solved

Redoing the counting column

Hello,

I am stucked trying to generate a counting column (in this example called 'DESIRED') from another (called 'Count') which it is not fitted proplerly due to the conditions especified into the DAX formula.

ElementyearWeekDate Order n# Count DESIRED
A20224616/11/2022 706891 14 14
A20224615/11/2022 435975 13 13
A20224615/11/2022 481813 13 12
A20224614/11/2022 677269 11 11
B20224618/11/2022 886314 7 7
B20224617/11/2022 763950 6 6
B20224616/11/2022 886081 5 5
B20224615/11/2022 527201 4 4
B20224614/11/2022 791914 3 3

The DAX expression for the 'Count' is made by using the function FILTER considering the date for the counting.
The problem comes when there are different orders for the same element and for the same day. It counts the total quantity til the date which it is correct but it is not the desired (I do not have hours or minutes to avoid this issue).

We can see in the row n# 2 and 3 that they have the same value in the field 'Count' (the third column should show 12 instead of 13 because it is considering the total counting until that date).
I add a new column called 'DESIRED' to show you what is what I am looking for.

I add this other example to improve the comprenhension of the case.

ElementyearWeekDate Order n# Count DESIRED
A20224510/11/2022 467301 10 10
A20224507/11/2022 963007 9 9
A20224507/11/2022 692324 9 8
A20224507/11/2022 899331 9 7
A20224507/11/2022 973904 9 6

 

**I add the column 'Order' because perhaps you find the way to solve it using somehow this field.

** The field 'order' is not ascending or descending day by day because there are different kind of orders.
**The 'Count' column is too complex. If it was possible to add another column such as the one added 'DESIRED', it would be great.
** Each 'Element' has its own 'Count'

Thank you very much in advance. 🙂



Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  jmateu88 ,

     

    Here are the steps you can follow：

    1. In Power query. Add Column – Index Column – From 1.

    2. Create calculated column.

    Column =
COUNTX(
    FILTER(ALL('Table'), 'Table'[Element]=EARLIER('Table'[Element])&&'Table'[year]=EARLIER('Table'[year])&&'Table'[Week]=EARLIER('Table'[Week])&&'Table'[Index]>=EARLIER('Table'[Index])),[Element])

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  jmateu88 ,

     

    Here are the steps you can follow：

    1. In Power query. Add Column – Index Column – From 1.

    2. Create calculated column.

    Column =
COUNTX(
    FILTER(ALL('Table'), 'Table'[Element]=EARLIER('Table'[Element])&&'Table'[year]=EARLIER('Table'[year])&&'Table'[Week]=EARLIER('Table'[Week])&&'Table'[Index]>=EARLIER('Table'[Index])),[Element])

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • jmateu88's avatar
    jmateu88
    Helper I
    3 years ago

    Hello Anonymous ,

    thank you for your answer.  The answer is correct if you can add the index in a table but, unfortunately, in this case, the table was made by using summarise and it was not possible to add the index from the original table (would create undesired extra rows or it would not be possible to relate because of multiple combinations).

    So, I got the idea of the index for using the 'order' to modify the time (I utilized hours because I do not expect to have a sum above 24 cases per day but it could be possible for minuts and it would be valid for 1440 cases for example).
    I would like to have not 'cheated' but it works. Here I share with all of you who might be interested;


    It is needed first count all the cases by day;

    Count_cases_same_day = 

var element = 'Table'[Element]
var fecha = 'Table'[Date]
var orden = 'Table'[Order n#]
return

COUNTX(
    FILTER(
        'Table',
        'Table'[Element] = element && 'Table'[Date] = fecha && 'Table'[Order n#] <= orden
    ),
    'Table'[Element]
)

     

    After this, modify the 'date';

    Fecha_acond = 

'Table'[Date] + TIME('Table'[Count_cases_same_day],0,0)

     

    Finally, the counter desired;

    Count_DESIRED = 
var element = 'Table'[Element]
var fecha = 'Table'[Fecha_acond]
return

COUNTX(
    FILTER(
        'Table',
        'Table'[Element] = element && 'Table'[Fecha_acond] <= fecha
    ),
    'Table'[Order n#]
)

     

    The differences between the columns 'DESIRED' and 'Count_DESIRED', I did no specified but they are not important because I do not care at all the order inside the day but the correct count by day.

    Thank you very much once more.