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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kish1999
Helper II
Helper II

Determining month of an Issue

Hello All,

 

I have a requirement where I have to determing the no of days an issue takes to be completed and show it on a graph Month on Month. I have written a measure to determine the no of days: 

Velocity1 =
Var  Inp = Calculate(MAX(Table[Date]), Table[STATUS] = "In Progress")
Var Acc = Calculate(MAX(Table[Date]), Table[Date] = "Accepted")
Return
CALCULATE(NETWORKDAYS(Inp,Acc,1))
 
The issue is If an issue started in July and completed in August, That issue is being shown in the month of both July and August. For example the below issue will be shown in both the months, while I would want it to show only in the month of July.
IssueStatusDate
AIn Progress21-07-2023
ACompleted03-08-2023

 

Can someone please help me with this requirement.

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Kish1999 

 

You can try the following methods.
Measure:

Velocity1 = 
Var _Inp = Calculate(MAX('Table'[Date]), FILTER(ALLEXCEPT('Table','Table'[Issue]),'Table'[Status] = "In Progress"))
Var _Com = Calculate(MAX('Table'[Date]), FILTER(ALLEXCEPT('Table','Table'[Issue]),'Table'[Status] = "Completed"))
Var _Days=CALCULATE(NETWORKDAYS(_Inp,_Com,1))
Var _StartDate=CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Issue]),'Table'[Status] = "In Progress"))
Return
IF(SELECTEDVALUE('Table'[Date])=_StartDate,_Days)

vzhangti_0-1698143313282.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Kish1999 

 

You can try the following methods.
Measure:

Velocity1 = 
Var _Inp = Calculate(MAX('Table'[Date]), FILTER(ALLEXCEPT('Table','Table'[Issue]),'Table'[Status] = "In Progress"))
Var _Com = Calculate(MAX('Table'[Date]), FILTER(ALLEXCEPT('Table','Table'[Issue]),'Table'[Status] = "Completed"))
Var _Days=CALCULATE(NETWORKDAYS(_Inp,_Com,1))
Var _StartDate=CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Issue]),'Table'[Status] = "In Progress"))
Return
IF(SELECTEDVALUE('Table'[Date])=_StartDate,_Days)

vzhangti_0-1698143313282.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Ahmedx
Super User
Super User

pls try this

 

Velocity1 =
Var  Inp = Calculate(MAX(Table[Date]), Table[STATUS] = "In Progress")
Var Acc = Calculate(MAX(Table[Date]), Table[Date] = "Accepted")
Return
IF(MAX(Table[Date])=Inp, // or MAX('Calendar'[Date])=Inp
CALCULATE(NETWORKDAYS(Inp,Acc,1)))

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors