Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

     

    Hi Anonymous ,

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Ashish_Mathur
      Super User
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     

    Hi Anonymous ,

     

    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