- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Average Days
Hi everyone,
Can you help me create dax for below? I need to create a measure to calculate the average days of processing date for system ID 0000-0000-0000. So let say for the first two rows, I want to calculate how many days do we get from 02/06/2018 to 08/08/2018? and the average per month? Thanks in advance!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @JB17 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create measures.
ProcessingDaysDifference =
VAR CurrentDate = MAX('Table'[Processing Date])
VAR PreviousDate =
CALCULATE(
MAX('Table'[Processing Date]),
FILTER(
ALL('Table'),
'Table'[system ID]=MAX('Table'[system ID]) &&
'Table'[Processing Date] < CurrentDate
)
)
RETURN
DATEDIFF(PreviousDate, CurrentDate, DAY)
AverageDaysPerMonth =
var _min=CALCULATE(MIN('Table'[Processing Date]),FILTER(ALL('Table'),'Table'[system ID]=MAX('Table'[system ID]) && YEAR('Table'[Processing Date])=YEAR(MAX('Table'[Processing Date])) && MONTH('Table'[Processing Date])=MONTH(MAX('Table'[Processing Date]))))
var _max=CALCULATE(MAX('Table'[Processing Date]),FILTER(ALL('Table'),'Table'[system ID]=MAX('Table'[system ID]) && YEAR('Table'[Processing Date])=YEAR(MAX('Table'[Processing Date])) && MONTH('Table'[Processing Date])=MONTH(MAX('Table'[Processing Date]))))
var _day=IF( DATEDIFF(_min, _max, DAY)=0,1,DATEDIFF(_min, _max, DAY))
var _count=COUNTROWS(FILTER(ALL('Table'),'Table'[system ID]=MAX('Table'[system ID]) && YEAR('Table'[Processing Date])=YEAR(MAX('Table'[Processing Date])) && MONTH('Table'[Processing Date])=MONTH(MAX('Table'[Processing Date]))))
return DIVIDE(_day,_count,0)
(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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @JB17 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create measures.
ProcessingDaysDifference =
VAR CurrentDate = MAX('Table'[Processing Date])
VAR PreviousDate =
CALCULATE(
MAX('Table'[Processing Date]),
FILTER(
ALL('Table'),
'Table'[system ID]=MAX('Table'[system ID]) &&
'Table'[Processing Date] < CurrentDate
)
)
RETURN
DATEDIFF(PreviousDate, CurrentDate, DAY)
AverageDaysPerMonth =
var _min=CALCULATE(MIN('Table'[Processing Date]),FILTER(ALL('Table'),'Table'[system ID]=MAX('Table'[system ID]) && YEAR('Table'[Processing Date])=YEAR(MAX('Table'[Processing Date])) && MONTH('Table'[Processing Date])=MONTH(MAX('Table'[Processing Date]))))
var _max=CALCULATE(MAX('Table'[Processing Date]),FILTER(ALL('Table'),'Table'[system ID]=MAX('Table'[system ID]) && YEAR('Table'[Processing Date])=YEAR(MAX('Table'[Processing Date])) && MONTH('Table'[Processing Date])=MONTH(MAX('Table'[Processing Date]))))
var _day=IF( DATEDIFF(_min, _max, DAY)=0,1,DATEDIFF(_min, _max, DAY))
var _count=COUNTROWS(FILTER(ALL('Table'),'Table'[system ID]=MAX('Table'[system ID]) && YEAR('Table'[Processing Date])=YEAR(MAX('Table'[Processing Date])) && MONTH('Table'[Processing Date])=MONTH(MAX('Table'[Processing Date]))))
return DIVIDE(_day,_count,0)
(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.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
11-20-2024 04:07 PM | |||
09-25-2024 05:41 AM | |||
12-18-2024 03:29 AM | |||
01-16-2025 02:21 AM | |||
05-21-2024 09:39 PM |
User | Count |
---|---|
83 | |
80 | |
46 | |
38 | |
37 |