cancel
Showing results for
Did you mean:
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 Id Date State 1143 6/17/21 Build 1143 6/18/21 Build 1143 6/19/21 Build 1143 6/20/21 Build 1143 6/21/21 Build 1143 6/22/21 Build 1143 6/23/21 UAT 1143 6/24/21 UAT 1143 6/25/21 UAT 1143 6/26/21 UAT 1143 6/27/21 UAT 1143 6/28/21 UAT 1143 6/29/21 UAT 1143 6/30/21 Closed 1143 7/1/21 Closed 1143 7/2/21 Closed

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

1 ACCEPTED SOLUTION
Community Support

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]))))``````

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

2 REPLIES 2
Community Support

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]))))``````

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

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 )

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!