Trending Late Values Over Time

[ajr5285]

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!

[ajr5285 · Accepted solution]

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. 

[v-venuppu]

Hi @ajr5285 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

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.

[Ashish_Mathur]

Hi,

How should delay be calcualted - completed months?  So for A, from 1/14/2025 to last date of selected month?

[ajr5285]

Yes.  That formula I am not having trouble with.  See my message to Irwan clarifying my question.

[Ashish_Mathur]

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.

[Irwan]

hello @ajr5285 

what formula to get 2 for B when October and 2.5 for A and C when April?

Also when choosing April, it means 1-April just in case date time value calculation.

Thank you.

[ajr5285]

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.

[Irwan]

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.

[ajr5285]

Thanks, I don't think I was clear om what I am looking for. 

I want to have a plot of average months late (y-axis) vs months (x-axis) with different trend lines for each part.  I have a slicer that you can select the months you want shown.  My actual data set has many lines of repeating parts with different completion and due dates. I need to be able to calculate the average months late by part quantity in each month and then display them.