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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors