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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Urgent calculate datediff same column

Hello, guys!

 

I need help to calculate a range of days for combining some of these statuses.

As an example:

-ID 8359: The interval between the "new" and "prioritized" must be 37 days;

-ID 8359: The interval between the "approved" and "prioritized" must be 1 day;

-ID 8490: The interval between the "approved" and "prioritized" must be 21 days;

 

835904/09/2019New
835910/10/2019Approved
835911/10/2019Prioritized
835914/10/2019In Progress
835930/10/2019Done
849011/09/2019New
849010/10/2019Approved
849031/10/2019Prioritized
849004/11/2019In Progress
1 ACCEPTED SOLUTION

Hi @Anonymous ,

Please try this formula.

Measure = AVERAGEX(VALUES('table'[ID]),'table'[interval Approved & Prioritized])

4.PNG 

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
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

6 REPLIES 6
v-xuding-msft
Community Support
Community Support

Hi @Anonymous ,

Please reference my sample to have a try.

New = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "New" ))
interval New & Prioritized = 
var b = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "Prioritized" ))
return
DATEDIFF([New],b,DAY)

Approved = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "Approved" ))
interval Approved & Prioritized = 
var b = CALCULATE(MAX('table'[date]),FILTER('table','table'[status] = "Prioritized" ))
return
DATEDIFF([Approved],b,DAY)

3.PNG

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

What if I wanted to have an average by status after calculating the day difference of all IDs?  In this case for example: approved & prioritized = (1+21)/2 = 11. 

Anonymous
Not applicable

@v-xuding-msft : Do we need to use Group by or CURRENTGROUP() in this context?

What if one of the status/date is not exist ?

amitchandak
Super User
Super User

create measures like this.

 

new date = calculate(min('table'[Admission date ]), status = "new")
prioritized date= calculate( max('table'[Discharge Date]), status = "new")

date diff = 
Avergae(
    VALUES(table[ID])
    , datediff( new date,Min prioritized date,days)
)

 

You will diff at your table id level. And post that you can take action. On top of datediff you can put if condition

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Anonymous
Not applicable

What if I wanted to have an average by status after calculating the day difference of all IDs?  In this case for example: approved & prioritized = (1+21)/2 = 11. 

Hi @Anonymous ,

Please try this formula.

Measure = AVERAGEX(VALUES('table'[ID]),'table'[interval Approved & Prioritized])

4.PNG 

Best Regards,

Xue Ding

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.