Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
DOC | Creation Date | Submittion Date | End of Month | Aging Oct | Aging Nov | Aging Dec |
1 | 11-Oct | 31-Oct | 20 | 50 | 81 | |
2 | 2-Nov | 30-Nov | 28 | 59 | ||
3 | 6-Dec | 31-Dec | 25 | |||
20 | 39 | 55 |
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |