Redoing the counting column

[jmateu88]

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.

Element	year	Week	Date		Order n#		Count		DESIRED
A	2022	46	16/11/2022		706891		14		14
A	2022	46	15/11/2022		435975		13		13
A	2022	46	15/11/2022		481813		13		12
A	2022	46	14/11/2022		677269		11		11
B	2022	46	18/11/2022		886314		7		7
B	2022	46	17/11/2022		763950		6		6
B	2022	46	16/11/2022		886081		5		5
B	2022	46	15/11/2022		527201		4		4
B	2022	46	14/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.

Element	year	Week	Date		Order n#		Count		DESIRED
A	2022	45	10/11/2022		467301		10		10
A	2022	45	07/11/2022		963007		9		9
A	2022	45	07/11/2022		692324		9		8
A	2022	45	07/11/2022		899331		9		7
A	2022	45	07/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. 🙂

[jmateu88]

Hello @v-yangliu-msft ,

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.

[v-yangliu-msft · Accepted solution]

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