March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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!
Solved! Go to 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!
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!
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" ) )
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |