How to count changes of values in a column by group and order by date

[Danielecc]

Hi community, 

I need to know (using DAX creating a calculated column) how to count changes of value in a column by group and order by date.

In this Image (is not sorted), if you sort by "Usuario" and "Fecha" the "Usuario" ALAN only change of value 2 times, and the "usuario" DANIEL change 7 times, then the RESULT EXPECTED is create the "Calculated Column" with this data.

Add in text mode the data, but the real file have more columns...

Fecha	Usuario	Equipo	Calculated Column
25-05-2022	ALAN	ATARGARONA	2
25-05-2022	ALAN	ATARGARONA	2
10-05-2022	ALAN	ATARGARONA	2
10-05-2022	ALAN	ATARGARONA	2
10-05-2022	ALAN	ATARGARONA	2
25-05-2022	ALAN	ATARGARONA	2
25-05-2022	ALAN	ATARGARONA	2
10-05-2022	ALAN	ATARGARONA	2
10-05-2022	ALAN	ATARGARONA	2
30-03-2022	ALAN	ATARGARONA	2
30-03-2022	ALAN	ATARGARONA	2
30-03-2022	ALAN	ATARGARONA	2
03-02-2021	ALAN	ATARGARONANB	2
03-02-2021	ALAN	ATARGARONANB	2
03-02-2021	ALAN	ATARGARONANB	2
03-02-2021	ALAN	ATARGARONANB	2
03-02-2021	ALAN	ATARGARONANB	2
12-01-2022	ALAN	ATARGARONA	2
12-01-2022	ALAN	ATARGARONA	2
31-01-2022	ALAN	ATARGARONA	2
31-01-2022	ALAN	ATARGARONA	2
06-01-2021	ALAN	ATARGARONANB	2
06-01-2021	ALAN	ATARGARONANB	2
06-01-2021	ALAN	ATARGARONANB	2
06-01-2021	ALAN	ATARGARONANB	2
06-01-2021	ALAN	ATARGARONANB	2
06-01-2021	DANIEL	DANIEL-NB	7
06-01-2021	DANIEL	DANIEL-NB	7
06-01-2021	DANIEL	DANIEL-NB	7
06-01-2021	DANIEL	MARTIN-PC	7
06-01-2021	DANIEL	MARTIN-PC	7
03-02-2021	DANIEL	DANIEL-NB	7
03-02-2021	DANIEL	DANIEL-NB	7
03-02-2021	DANIEL	DANIEL-NB	7
03-02-2021	DANIEL	DANIEL-NB	7
03-02-2021	DANIEL	DANIEL-NB	7
12-01-2022	DANIEL	MARTIN-PC	7
12-01-2022	DANIEL	MARTIN-PC	7
31-01-2022	DANIEL	DANIEL-NB	7
31-01-2022	DANIEL	DANIEL-NB	7
30-03-2022	DANIEL	DANIEL-NB	7
30-03-2022	DANIEL	JUAN-NB	7
30-03-2022	DANIEL	JUAN-NB	7
30-03-2022	DANIEL	JUAN-NB	7
10-05-2022	DANIEL	DANIEL-NB	7
10-05-2022	DANIEL	DANIEL-NB	7
10-05-2022	DANIEL	DANIEL-NB	7
25-05-2022	DANIEL	DANIEL-NB	7
25-05-2022	DANIEL	DANIEL-NB	7

Thanks for your help, best regards...

[v-henryk-mstf]

Hi @Danielecc ,

Try to use ALLEXCEPT instead of ALL function for group sorting.

Check out the following formula:

Calculated1 = CALCULATE(COUNTROWS(T),ALLEXCEPT(T,T[Usuario],T[Fecha]))

Calculated2 =
CALCULATE (
    COUNTROWS ( T ),
    FILTER (
        ALL ( T ),
        T[Usuario] = MAX ( T[Usuario] )
            && T[Fecha] = MAX ( T[Fecha] )
    )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Danielecc]

Hi Henrry, First of all, thanks for your answer. 

I still have the problem, but I dont have an error, the "calculated 1" give me values and "calculated 2" only give me "blank" in all the rows of that column.

I was trying something like a RANK using "Fecha" to order by date or something similar.

[lbendlin]

Your date column is not granular enough for reliable sorting. Please resubmit either with an index column or better granularity (including time, for example)