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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.