Need help

[viveksharma21]

Hello everyone, i am looking for some help in writting a DAX that will give me the following result:

Ideal result = calculate number of activity IDs that have changed status from Not Started to In Progress between Sep 15 and Sep 16.

Ideally i would like to compare this measure for more than two days

Sample Dataset is here:

Date	Activity ID	Activity Status
15 sep 2021	A	Not Started
15 sep 2021	B	Not Started
15 sep 2021	C	Not Started
15 sep 2021	D	In Progress
15 sep 2021	E	In Progress
16 sep 2021	A	Not Started
16 sep 2021	B	In Progress
16 sep 2021	C	In Progress
16 sep 2021	D	In Progress
16 sep 2021	E	In Progress

[v-rongtiep-msft · Accepted solution]

Hi @viveksharma21 ,

Create two measures Please refer the following formulas.

answer =

var ss =

CALCULATE(MAX('Table'[Activity Status]),FILTER(ALL('Table'),'Table'[Date]=SELECTEDVALUE('Table'[Date])-1&&'Table'[Activity ID]=SELECTEDVALUE('Table'[Activity ID])))

var aa =

IF(ISBLANK(ss),SELECTEDVALUE('Table'[Activity Status]),ss)

var change = IF(SELECTEDVALUE('Table'[Activity Status])=aa,0,1)

return change

times = SUMX(ALLEXCEPT('Table','Table'[Date]),[answer])

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

Best Regards

Community Support Team _ Polly

[viveksharma21]

maybe I can add some more clarity. I want to finallly see which Activity ID was progressed from "Not Started" to "In Progress"

[viveksharma21]

I want to plot the measure over the dates in column 1. this data will grow over many dates as new schedule comes in.

[Ashish_Mathur]

I am not clear.  Please show the expected resul very clearly and also the input that you will be providing via slicers.

[Ashish_Mathur]

Hi,

So in a slicer, will you be selecting 2 dates?