Time Intelligence - Last Date Overdue

[GuidoPinares]

Hi Community,

Thanks for your time and sorry if this question is easy to solve, I am just starting in this world of Power BI and I need some help. Thanks in advance for any suggestion.

I have a table in an excel file that containts 4 different fields:

Customer Name, Prescription Number, Days and Date Dispensed.

This is based on a pharmacy so the customer has a prescription and this table also store the days for the next refill and the date when the prescription was dispensed.

I want to add to that table in power BI two more columns:

Next refill: This will take in consideration the Customer and the date, using only the last date + Days for that record.

Overdue: This column will consider the previous one (NextRefill + 15 days(Overdue range)) and if is less than TODAY it is overdue and it should show TRUE or FALSE.

Thanks for the help!

[sevenhills · Accepted solution]

I see that you said as new to Power BI, sharing this tip: Column value is persisted when you refresh the data. whereas Measure is calculated as and when needed.

I am providing the column syntax for you: (little bit detailed steps for your understanding)

Next Refill = 

var _Cust = 'Table'[Customer]
var _DateDisp = 'Table'[Date Dispensed] 

var _MaxDate = CALCULATE( Max('Table'[Date Dispensed]), FILTER( all('Table'), 'Table'[Customer] = _Cust)) 
var _SameMaxDateRow = IF ( _DateDisp = _MaxDate, True, False)

Return IF( _SameMaxDateRow , _MaxDate + 'Table'[Days], BLANK())

Overdue = 
 var _bufferDt = 'Table'[Next Refill] 
 var _IsOverdue = IF ( _bufferDt >= TODAY() + 15, "FALSE", "TRUE" )
 
 Return IF (  IsBlank('Table'[Next Refill]), BLANK(), _IsOverdue)

FYI: Overdue has to be data type as text. If the data type is True/false, then all the blank values become false. 

Hope this helps!

[GuidoPinares]

Thanks sooooo much! this really help me understand the logic and also solve the problem.

[sevenhills]

Glad it worked! Thank you

[sevenhills]

Simplified version:

Next Refill = 

var _Cust = 'Table'[Customer]
var _MaxDate = CALCULATE( Max('Table'[Date Dispensed]), FILTER( 'Table', 'Table'[Customer] = _Cust))  

Return IF( 'Table'[Date Dispensed] = _MaxDate , _MaxDate + 'Table'[Days], BLANK())

Overdue = IF (  IsBlank('Table'[Next Refill]), BLANK(), IF ( 'Table'[Next Refill]  >= TODAY() + 15, "FALSE", "TRUE" ) )