cancel
Showing results 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

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.
 Issue Status Date A In Progress 21-07-2023 A Completed 03-08-2023

1 ACCEPTED SOLUTION
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)``````

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.

2 REPLIES 2
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)``````

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.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.