Forum Discussion

GuidoPinares's avatar
GuidoPinares
Helper I
3 years ago
Solved

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.

 

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!

 

 

Need Help
Tips and Tricks
  • sevenhills's avatar
    sevenhills
    3 years ago

    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!

     

4 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    3 years ago

    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!

     

    • sevenhills's avatar
      sevenhills
      Super User
      3 years ago

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

       

    • GuidoPinares's avatar
      GuidoPinares
      Helper I
      3 years ago

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