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
rbangari001
Frequent Visitor

Calculate the no of days for a Epic which was spent in each state but NOT for "Closed" State

Hi,

 

I have created a Dax formula to calculate the "No. of days which was spent in each state". Now I dont want to see the ""No. of days which was spent in CLOSED state"

 

Below is the formula which I have used

 

Index=

CALCULATE(DISTINCTCOUNT('Epic - All History Daily'[Date]),
FILTER('Epic - All History Daily',
'Epic - All History Daily'[State]= EARLIER('Epic - All History Daily'[State]) &&
'Epic - All History Daily'[Date] <=EARLIER( 'Epic - All History Daily'[Date])))

 

 

Days Spent =
VAR vKEY = 'Epic - All History Daily'[Work Item Id]
VAR vINDEX = 'Epic - All History Daily'[Index]
VAR vState = 'Epic - All History Daily'[Phase]
RETURN
DATEDIFF(
CALCULATE( VALUES('Epic - All History Daily'[Date]),
FILTER( ALL('Epic - All History Daily'),
'Epic - All History Daily'[Work Item Id] = vKEY &&
'Epic - All History Daily'[Index] = (vINDEX - 1) &&
'Epic - All History Daily'[Phase] = vState
)
),'Epic - All History Daily'[Date],DAY)
 
Please let me know how should avoid calculating the No. of days for "Closed" State
 
Work Item IdDateState
11436/17/21Build
11436/18/21Build
11436/19/21Build
11436/20/21Build
11436/21/21Build
11436/22/21Build
11436/23/21UAT
11436/24/21UAT
11436/25/21UAT
11436/26/21UAT
11436/27/21UAT
11436/28/21UAT
11436/29/21UAT
11436/30/21Closed
11437/1/21Closed
11437/2/21Closed

 

 

@v-jingzhan @BA_Pete  @v-jingzhang  @v-stephen-msft  @v-yingjl 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @rbangari001 

 

Is this what you want?

Index = IF('Epic - All History Daily'[State]="Closed",BLANK(),
CALCULATE(DISTINCTCOUNT('Epic - All History Daily'[Date]),
FILTER('Epic - All History Daily',
'Epic - All History Daily'[State]= EARLIER('Epic - All History Daily'[State]) &&
'Epic - All History Daily'[Date] <=EARLIER( 'Epic - All History Daily'[Date]))))

21120901.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @rbangari001 

 

Is this what you want?

Index = IF('Epic - All History Daily'[State]="Closed",BLANK(),
CALCULATE(DISTINCTCOUNT('Epic - All History Daily'[Date]),
FILTER('Epic - All History Daily',
'Epic - All History Daily'[State]= EARLIER('Epic - All History Daily'[State]) &&
'Epic - All History Daily'[Date] <=EARLIER( 'Epic - All History Daily'[Date]))))

21120901.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

amitchandak
Super User
Super User

@rbangari001 , Try a new measure like

 

Sumx(Summarize(Table, Table[Work Item Id], Table[State], "_min" , min (Table[Date]) , "_max" , max(Table[Date])), datediff(_min, _max,day) +1 )

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.