Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pratafran
Helper III
Helper III

Cummulative Aging based on month

Hello community!,

 

I have the following problem:

 

In a Table, I have a list of documents with events (Dates), let's simplify it in two: Creation Date, Submission Date.

 

There is an Status based on the Submission event. If it is BLANK(), it is Pending, otherwise it is submitted.

 

Now, I want to calculate the Aging of the backlog (documents not submitted), what is very easy at a row level = DATEDIFF ([Creation Date],TODAY(),DAY) but I need to do it dynamic, based on the month of analysis (on a measure).

 

I have a Calendar Table linked, and now, I want on a chart, monitoring the evolution of the backlog and its aging based on the creation date.

 

Somethng like this: In bars (docs submitted and docs pending to submit). In the Line, the cumulative aging of the pending ones that should calculate up the the end of each month or today if it was created on the current month.

pratafran_0-1675086421043.png

 

In the example table below, I want to draw in the line chart, the 20, 39 and 55 days, that is calculated based on the cumulative aging of the pending docs, but recalcualted each month.

DOCCreation DateSubmittion DateEnd of MonthAging OctAging NovAging Dec
111-Oct 31-Oct205081
22-Nov 30-Nov 2859
36-Dec 31-Dec  25
    203955

 

I have tried the following measure but I'm not getting the result I want

 

 

 

Aging Submittion= 
VAR _TableBacklog = 
    SUMMARIZE(
        FILTER(Table[Status] = "Pending"),
        Table[Doc no],
        Table[Creation Date],
        "Backlog Date",IF(
                            YEAR(Table[Creation Date])=YEAR(TODAY()) 
                            && MONTH(Table[Creation Date]) = MONTH(TODAY()),
                            TODAY(),
                            MAXX(ALLSELECTED(Calendar), Calendar[Date] )
                        )
    )
VAR _max = MAXX(ALLSELECTED(Calendar), Calendar[Date] )
RETURN
 CALCULATE (
     AVERAGEX(
        _TableBacklog,
        DATEDIFF(Table[Creation Date],[Backlog Date],DAY)
     ),
     FILTER(all(Calendar),Calendar[Date] <=_max)

 

 

 

0 REPLIES 0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.