Forum Discussion

ajr5285's avatar
ajr5285
Regular Visitor
10 months ago
Solved

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   ...
Need Help
  • ajr5285's avatar
    ajr5285
    9 months ago

    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. 
Irwan's avatar
Irwan
Super User
10 months ago

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's avatar
ajr5285
Regular Visitor
10 months ago

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's avatar
    Irwan
    Super User
    10 months ago

    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's avatar
      ajr5285
      Regular Visitor
      10 months ago

      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.