cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viveksharma21
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:

DateActivity IDActivity Status
15 sep 2021ANot Started
15 sep 2021   BNot Started
15 sep 2021   CNot Started
15 sep 2021   DIn Progress
15 sep 2021   EIn Progress
16 sep 2021   ANot Started
16 sep 2021   BIn Progress
16 sep 2021   CIn Progress
16 sep 2021   DIn Progress
16 sep 2021   EIn Progress
1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

 

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

11.PNG

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

22.PNG

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

View solution in original post

5 REPLIES 5
v-polly-msft
Community Support
Community Support

 

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

11.PNG

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

22.PNG

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

View solution in original post

viveksharma21
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"

viveksharma21
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.

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
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors