cancel
Showing results for
Did you mean:
Frequent Visitor

## Need help

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
1 ACCEPTED SOLUTION
Community Support

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

5 REPLIES 5
Community Support

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

Frequent Visitor

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

Frequent Visitor

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.