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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
GuidoPinares
Helper I
Helper I

Time Intelligence - Last Date Overdue

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.

 

GuidoPinares_0-1683306221693.png

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!

 

 

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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)

 

sevenhills_0-1683315141872.png

 

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!

 

View solution in original post

4 REPLIES 4
sevenhills
Super User
Super User

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)

 

sevenhills_0-1683315141872.png

 

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!

 

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

Glad it worked! Thank you

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" ) )

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.