Forum Discussion
Trending Late Values Over Time
Hi,
I am looking for a way to trend auto calcualted values over time. For a data set example:
| Part | Due Date | Completed Date | Months Late Today |
| A | 1/14/25 | 6/14/25 | |
| B | 8/14/25 | 2 | |
| C | 2/14/25 | 5/14/25 |
So if I was to choose October, only part B would show up with a avg months late of 2. If I choose April, an avg months late would show 2.5 with Parts A and C.
Any ideas on how to calculate a new value based on a selection? Ideally I would like to be able to plot an average over the year.
Thanks in advance!
I created a way but it is a lot of manual input.
1. First I created a simple yes (value 1) or no (value 0) column if the part was ever late.
2. I then created calculated columns for every month in a year. This tracks the days late on each row for the respective month. So I have a 202501 Days Late, 202502 Days Late, etc for each item.
3. I then had to create a new table to get the date into each row. This creates multiple rows for each part, but has the late value based on the month.
- CombinedSummaryTable =filter( union(summarize('BaseTable','BaseTable'[Part],'BaseTable'[202501DaysLate],'BaseTable'[Qty],"Date", "2025-01-31",Avg Months", round(average('BaseTable'[202501DaysLate]/30.44,0)),.......{repeat for other months}, not(isblank('BaseTable'[202501DaysLate])
Now I can use a date slicer and another slicer for the Part to see late values over time.
- CombinedSummaryTable =filter( union(summarize('BaseTable','BaseTable'[Part],'BaseTable'[202501DaysLate],'BaseTable'[Qty],"Date", "2025-01-31",Avg Months", round(average('BaseTable'[202501DaysLate]/30.44,0)),.......{repeat for other months}, not(isblank('BaseTable'[202501DaysLate])
10 Replies
There is one part (B) late in October by 2 months. The average is then 2 divided by 1. For April, 2 parts are late (A and C), with 3 and 2 months late, respectively; therefore the average is 5/2=2.5.
Thats good to know which date is chosen, but the probelm would still be the same just one month off each part.
hello ajr5285
for the selection, i kinda confused because you want to show Part A and Part C when select April. While select October only show Part B.
regardless, for the calculation should be like below.
When blank completed date, then it will be calculate month average up to today.
Oct-Aug is 2 divided by 1 part.
When not blank, then it will calculate month average between due date and completed date.
June-Jan is 5 and May-Feb is 3 then divided by 2 parts.
Months Late Today =
var _Today =
AVERAGEX(
FILTER(
'Table',
ISBLANK('Table'[Completed Date])
),
DATEDIFF(
'Table'[Due Date],
TODAY(),
MONTH
)
)
var _Completed =
AVERAGEX(
ALL('Table'),
DATEDIFF(
'Table'[Due Date],
'Table'[Completed Date],
MONTH
)
)
Return
IF(
ISBLANK(SELECTEDVALUE('Table'[Completed Date])),
_Today,
_Completed
)Hope this will help.Thank you.
Hi,
How should delay be calcualted - completed months? So for A, from 1/14/2025 to last date of selected month?
Yes. That formula I am not having trouble with. See my message to Irwan clarifying my question.
Hi,
It will help if you share a few moe rows of data and show the expected result in a simple table format. From there, we should be able to build our desired visual.
Hi ajr5285 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Ashish_Mathur Irwan for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?If not, can you please share the requested details, so that it will be helpful for us to solve the issue.
Thank you.
I created a way but it is a lot of manual input.
1. First I created a simple yes (value 1) or no (value 0) column if the part was ever late.
2. I then created calculated columns for every month in a year. This tracks the days late on each row for the respective month. So I have a 202501 Days Late, 202502 Days Late, etc for each item.
3. I then had to create a new table to get the date into each row. This creates multiple rows for each part, but has the late value based on the month.
- CombinedSummaryTable =filter( union(summarize('BaseTable','BaseTable'[Part],'BaseTable'[202501DaysLate],'BaseTable'[Qty],"Date", "2025-01-31",Avg Months", round(average('BaseTable'[202501DaysLate]/30.44,0)),.......{repeat for other months}, not(isblank('BaseTable'[202501DaysLate])
Now I can use a date slicer and another slicer for the Part to see late values over time.
- CombinedSummaryTable =filter( union(summarize('BaseTable','BaseTable'[Part],'BaseTable'[202501DaysLate],'BaseTable'[Qty],"Date", "2025-01-31",Avg Months", round(average('BaseTable'[202501DaysLate]/30.44,0)),.......{repeat for other months}, not(isblank('BaseTable'[202501DaysLate])
