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

Calculating the Average between two dates

Hi there,

I am going to create a suite of SLA type reports based on a "Incident" data source.

I thought I could create a measure to calculate the average between the 'created date' and the 'resolved date' and then filter for teams and a date range, but I can't seem to get the formula calculation to work.

I assume a measure for each of these is the right way to go about it as the table is loaded with all the dates / times I need.

What would be the best way to calculate this in a measure to use for a line graph?

Thanks all.

1 ACCEPTED SOLUTION

Hi @Anonymous ,

First, you need to create a Date table and create a calculated column [IsWorkingDay] to recoginize the date is working day or not.

 

IsWorkingDay = NOT WEEKDAY( 'Date'[Date] ) IN { 1,7 }

 

Then create a calculated column as below in fact table to get the diff days between created date and resolved date. 

 

Day Diff =
CALCULATE (
    COUNTROWS ( 'Date' ),
    DATESBETWEEN ( 'Date'[Date], 'Table'[created date], 'Table'[resolved date] - 1 ),
    'Date'[IsWorkingDay] = TRUE,
    ALL ( 'Table' )
)

 

You can refer the content in the following links for the details.

POWER BI DATEDIFF ONLY WORKING DAYS, HOURS AND SO ON…

Counting working days in DAX

If the above one is not what you want, please provide some sample data in your model(exclude sensitive data) and your expected result with sample or screenshot. Thank you.

Best Regards

Community Support Team _ Rena
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
Syndicate_Admin
Administrator
Administrator

Hello I want to get an average frequency between the last 4 dates of sale, I am a beginner and I want to know some method.

Best regards

amitchandak
Super User
Super User

@Anonymous ,

You can create date diff as column

datediff(Table[created date], Table[resolved date], day)

 

and take average

 

or a measure

Averagex(Table,datediff(Table[created date], Table[resolved date], day))

 

How to deal if they are in two table, of you want to measure based on a level

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

Anonymous
Not applicable

ah I think I know what is wrong, I need to limit the measure to only be for resolved or closed status also which I have done in a filter on the measure, but now I am stuck with it including weekends.

Hi @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

yingyinr_0-1619509759244.png

Best Regards

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

Hi @Anonymous ,

First, you need to create a Date table and create a calculated column [IsWorkingDay] to recoginize the date is working day or not.

 

IsWorkingDay = NOT WEEKDAY( 'Date'[Date] ) IN { 1,7 }

 

Then create a calculated column as below in fact table to get the diff days between created date and resolved date. 

 

Day Diff =
CALCULATE (
    COUNTROWS ( 'Date' ),
    DATESBETWEEN ( 'Date'[Date], 'Table'[created date], 'Table'[resolved date] - 1 ),
    'Date'[IsWorkingDay] = TRUE,
    ALL ( 'Table' )
)

 

You can refer the content in the following links for the details.

POWER BI DATEDIFF ONLY WORKING DAYS, HOURS AND SO ON…

Counting working days in DAX

If the above one is not what you want, please provide some sample data in your model(exclude sensitive data) and your expected result with sample or screenshot. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for the breakdown, that is really helpful info.

 

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.