Calculated column

[Anonymous]

Hey Everyone. Please l want to find dax that can count the number of cycles of a battery for each component. For each component, a cycle corresponds to a period where the intensity has a cnstant sign and when the intensity changes sign, we go to another cycle. It is therefore necessary to make a calculated column which will identify the cycles of each component, how can l do that? 

Thanks in advance for the suggestions.

component	intensity	number of cycle
1	-1	1
1	-2	1
1	3	2
1	4	2
0	4	1
0	5	1
0	-1	2
0	-3	2

En français:

je veux trouver une formule qui permet de compter le nombre de cycle d'une batterie pour chaque composant. Pour chaque composant  un cycle correspond à une période où l'intensité a un signe constant et lorsque l'intensité change de signe on passe à un autre cycle.

il faut donc faire une colonne calculée qui va repérer les cycles de chaque composant, comment je peux faire ça? Merci d'avance pour les propositions.

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is a sample file with the solution https://we.tl/t-K6pFsGb1qn
It includes 2 calculated columns. The first check for change of sign (checks for new cycle) 

New Cycle = 
VAR CurrentDate = Data[Date]
VAR CurrentComponentTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[component] ) )
VAR T1 = FILTER ( CurrentComponentTable, Data[Date] < CurrentDate )
VAR CurrentIntensity = Data[intensity]
VAR PreviousDate = MAXX ( T1 , Data[Date] )
VAR PreviousIntensity = MAXX ( FILTER ( T1, Data[Date] = PreviousDate ), Data[intensity] )
RETURN
    IF ( ISBLANK ( PreviousIntensity ) || PreviousIntensity * CurrentIntensity < 0, 1, 0 )

The 2nd column is the cycle sequential number

cycle # = 
VAR CurrentDate = Data[Date]
VAR CurrentComponentTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[component] ) )
VAR T1 = FILTER ( CurrentComponentTable, Data[Date] <= CurrentDate )
RETURN
    SUMX ( T1, [New Cycle] )

[Anonymous]

l don't know why but the second column display the total number of cycles of a day. What l want it's to display the evolution of cycle , for example seeing the 1st cycle then the 2nd then 3rd cycle ,not having the total number directly. is it possible?

[tamerj1]

@Anonymous 

It wirks in the file that I shared. Can share screenshots?

[Anonymous]

😁@tamerj1 

I will try it. Thank you for your suggestion.

[Anonymous]

Bien galère le cas de figure 🙂

[Anonymous]

j'ai remplacé la colonne date par la colonne date et heure et ça a marché.  C'est parceque dans ton tableau pour une date tu as juste compté un cycle pour chaque composant. 

Merci pour la formule🤗.

[tamerj1]

@Anonymous 

Yes you are I forgot to mention that. 

[Anonymous]

And we can have many cycles in one day.

[Anonymous]

Hi Alexa,

Do you have a dte column in this table ?

[Anonymous]

yes l have a date column.