Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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-rongtiep-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-rongtiep-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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.