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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.