How to Calculate Days Past Due

[Gasdetect40]

Good Day to All!

I would like to create a donut or pie chart showing the count of rental assets past due by: 2 days, 7 days and 15 days. Each count would have a different colour. 

I've transformed my data to edit out the unessisary colums as seen below. I just have the item type and the expected renturn date of the item left. I just want to know how many units are past due by the above mentioned days. Any ideas? Would it be better to do it in the source data (excel online) It may be important to note that this will change daily as items are due or fall past due.Transformed DataSource Data

[littlemojopuppy · Accepted solution]

Hi @Gasdetect40 

I'm going to suggest an answer using DAX, not Power Query.  Create a calculated column to calculate the number of days overdue.  And then a second column to group them.

DaysOverdue =
DATEDIFF(
	[ExpectedReturnDate],
	TODAY(),
	DAY
)


DaysOverdueGrouping =
SWITCH(
	TRUE(),
	Table[DaysOverdue] <= 2, "2 Days",
	AND(
		Table[DaysOverdue] > 2,
		Table[DaysOverdue] <= 7
	), "7 Days",
	AND(
		Table[DaysOverdue] > 7,
		Table[DaysOverdue] <= 15
	), "15 Days",
	"15+ Days"
)
		

I'm not sure of your table and field names so check those.  And check that the logic for each grouping is correct.

Hope this helps!

[littlemojopuppy]

@Gasdetect40 going to amend my response.  I'm used to working in Analysis Services, which doesn't allow grouping/binning the same way Power BI Desktop does.  You don't really have to create the second column if you want to use the grouping feature in desktop.