Forum Discussion

Gasdetect40's avatar
Gasdetect40
Frequent Visitor
4 years ago
Solved

How to Calculate Days Past Due

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 transf...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    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's avatar
littlemojopuppy
Community Champion
4 years ago

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's avatar
littlemojopuppy
Community Champion
4 years ago

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.