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

Frequent Visitor

## Trying to calculate Date Difference within the same date column based on mentioned Condition

Hi  - I am trying to cacluate Cycle Time which is the Date Difference between two dates in the same date column. The condition I want to check for is if the Singular current Row Value in Closed items is greater than the Total Items in the previous date. If yes, then I want Date Difference between the Current Date and the Future Date.

Here is sample data attached :

For Example :

1. Cycle Time for 01/03/2022 is 2 because Closed Items = 17 on 01/05/2022 is greater than Total Items 9 - so date diff between 01/05/2022 and 01/03/2022 is 2
2. Cycle Time for 01/07/2022 is 10 because Closed Items = 116 on 01/17/2022 is greater than Total Items = 110 etc

This is created in Excel for testing purpose, but ideally I want to be able to create a Calculated Measure or Calculated column that would help me get there. Thank you in advance!

Note : My date column has multiple date values for the same date - the table is showing Summation of closed & total items

This is my current measure but not giving me the right answer as variable tot is calculated incorrectly for some reason

"Cycle Time 14 Measure =
VAR currentdate = MAX(st_bugs[date])
VAR tot1 = MAXX(VALUES(st_bugs), st_bugs[total])
VAR futuredates =
CALCULATETABLE(
VALUES(st_bugs[date]),
st_bugs[date] > currentdate,
st_bugs[total_close] >= tot1
)
VAR minfuturedate =
MINX(
FILTER(futuredates, MAXX(VALUES(st_bugs), st_bugs[total]) >= tot1 ),
st_bugs[date]
)
RETURN
IF(NOT(ISBLANK(minfuturedate)), DATEDIFF(currentdate, minfuturedate, DAY))"

1 ACCEPTED SOLUTION
Community Support

Hi @kgosalia12 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures.

``_closed items = CALCULATE(SUM('Table'[closed items]),FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])))``
``_total items = CALCULATE(SUM('Table'[total items]),FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])))``
``````Measure =
var _a = SUMMARIZE(ALL('Table'),[date],"CI",[_closed items],"TI",[_total items])
var _b = [_total items]
var _c = MINX(FILTER(_a,[CI]>_b),[date])
return IF(NOT(ISBLANK(_c)),DATEDIFF(SELECTEDVALUE('Table'[date]),_c,DAY))``````

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

2 REPLIES 2
Frequent Visitor

THANK YOU SO MUCH! The solution provided by you works perfectly.

I had to modify the formulas to meet my data requirements - sharing it here incase it is useful for other people

This Calculates Closed Items

Measure = CALCULATE(SUM(st_bugs[total_close]),FILTER(ALLEXCEPT(st_bugs,st_bugs[priority],st_bugs[resolution],st_bugs[valid]),st_bugs[date]=MAX(st_bugs[date])))

This Calculates Open Items
Measure 2 = CALCULATE(SUM(st_bugs[total]),FILTER(ALLEXCEPT(st_bugs,st_bugs[priority],st_bugs[resolution],st_bugs[valid]),st_bugs[date]=MAX(st_bugs[date])))

This Calculates FINAL Cycle Time
Measure Final =
var _a = SUMMARIZE(ALLEXCEPT(st_bugs,st_bugs[priority],st_bugs[resolution],st_bugs[valid]),st_bugs[date],"CI",[Measure],"TI",[Measure 2])
var _b = [Measure 2]
var _c = MINX(FILTER(_a,[CI]>_b),[date])
return IF(NOT(ISBLANK(_c)),DATEDIFF(SELECTEDVALUE(st_bugs[date]),_c,DAY))

Thank you again! You have helped me alot with this.

Community Support

Hi @kgosalia12 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures.

``_closed items = CALCULATE(SUM('Table'[closed items]),FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])))``
``_total items = CALCULATE(SUM('Table'[total items]),FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])))``
``````Measure =
var _a = SUMMARIZE(ALL('Table'),[date],"CI",[_closed items],"TI",[_total items])
var _b = [_total items]
var _c = MINX(FILTER(_a,[CI]>_b),[date])
return IF(NOT(ISBLANK(_c)),DATEDIFF(SELECTEDVALUE('Table'[date]),_c,DAY))``````

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

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.