Measure to calculate number of changes

[ArashZ]

Hi,

I Have a table of users with their status, involving either active or inactive. Picture 1

Date	Status	Status Text	User
03-Mar	0	Active	A
04-Mar	1	Inactive	A
03-Mar	0	Active	B
04-Mar	0	Active	C
05-Mar	1	Inactive	C
05-Mar	0	Active	D

I want to create a graph that shows the number of users who have changed from active to inactive or vice versa.

For example, in this case, the number of users is 2. (Users A and C). In fact, users B and D didn't change and should not be counted.

Is there a way to have a calculated column or measure to solve that?

Best,

[ArashZ]

Hi,

I Have a table of users with their status, involving either active or inactive. Picture 1

Date	Status	Status Text	User
03-Mar	0	Active	A
04-Mar	1	Inactive	A
03-Mar	0	Active	B
04-Mar	0	Active	C
05-Mar	1	Inactive	C
05-Mar	0	Active	D

I want to create a graph that shows the number of users who have changed from active to inactive or vice versa.

For example, in this case, the number of users is 2. (Users A and C). In fact, users B and D didn't change and should not be counted.

Is there a way to have a calculated column or measure to solve that?

Best,

[v-cazheng-msft · Accepted solution]

Hi @ArashZ 

You can try the following steps.

1 Create two Calculated columns

count user = CALCULATE(COUNT('Table'[User]),ALLEXCEPT('Table','Table'[User]))

max date = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[User]))

2 Create two Measures

Activation to Deactivation = CALCULATE(COUNT('Table'[User]),FILTER('Table','Table'[count user]=2&&'Table'[Status]=1&&'Table'[Date]='Table'[max date]))

Deacrivation to Activation = CALCULATE(COUNT('Table'[User]),FILTER('Table','Table'[count user]=2&&'Table'[Status]=0&&'Table'[Date]='Table'[max date]))

The result looks like this:

Best Regards,

Caiyun Zheng

Is CaiyunhZhengwer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ArashZ , Create measure like, with help from date table

MTD = CALCULATE(Max(Table[Status]),DATESMTD('Date'[Date]))
last MTD = CALCULATE(Max(Table[Status ]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

status not same = countx(filter(values(Table[User]), [MTD] <> [Last MTD]),[User])

status same = countx(filter(values(Table[User]), [MTD] = [Last MTD]),[User])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[ArashZ]

@amitchandakHi,

When I created your measure, the "status same"measure shows 4, which is incorrect. There are only two users (A and B) that have changes. Also, when I create a graph with the "date" as Axis and "Status same" as a value, it shows wrong information.

[ArashZ]

Hi @amitchandak 

I will add more data for validation here:

Date Status StatusText User
2021-01-07 16:32 0 Activation A
2021-03-08 16:32 1 Deactivation A
2021-01-06 16:32 0 Activation B
2021-03-05 16:32 1 Deactivation B
2021-03-03 16:32 1 Deactivation H
2021-02-18 16:32 1 Deactivation G
2021-02-09 16:32 1 Deactivation J
2021-01-05 16:32 0 Activation V
2021-02-03 16:32 1 Deactivation E
2021-02-04 16:32 0 Activation E
2021-01-03 16:32 0 Activation P
2021-01-04 16:32 1 Deactivation P
2021-01-16 16:32 1 Deactivation L
2021-01-18 16:32 0 Activation L
2021-01-10 16:32 1 Deactivation T
2021-01-15 16:32 0 Activation T
2021-01-01 16:32 0 Activation Q
2021-01-02 16:32 1 Deactivation Q

I want to have a graph date on-axis and the number of changes in value. The date should reflect the change date (deactivation) not the activation date. For example, In March, we have two changes (User A and B)