Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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=
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
Solved! Go to Solution.
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]))))
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
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]))))
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
@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 )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |