Calculate column according to a different date table

Hi!

I would like to compare a date field from a table with a general date table that I am using as a slicer to filter data. Data table looks like this:

Report Due Date Overdue (Y/N)

#1 10/10/2019

#2 15/10/2019

So for example, if I select a data range in the slicer before 10/10/2019, "Overdue" column should be "N" for both reports.

If, data range ends between 10th and 15th Oct, #1 should be in overdue.

And finally if data range ends after 15th Oct, both reports should be ticked as "Overdue".

I have created the following formula after creating a relationship between tables, but it is not working as expected.

Overdue = IF('Table1'[Due Date]>RELATED(DateTable[Date]),"Overdue","on time")

Any help will be much appreciated!

Thank you in advance.

jthomson
6 years ago
Try pulling through the largest value in your date table as a variable and then compare your due date column to that?
lc_finance
6 years ago
Hi 21818 ,

As jthomson mentioned, the solution is to compare the maximum value of your slicer to the due date in your table.
Here is the formula you can use:
Overdue = VAR dueDate = SELECTEDVALUE('Table'[Due date]) RETURN IF(MAX('Dates'[Date])<dueDate,"N","Overdue")
And a screenshot showing the end result

Regards,

LC
Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

jthomson
Solution Sage
6 years ago
Try pulling through the largest value in your date table as a variable and then compare your due date column to that?
lc_finance
Solution Sage
6 years ago
Hi 21818 ,

As jthomson mentioned, the solution is to compare the maximum value of your slicer to the due date in your table.
Here is the formula you can use:
Overdue = VAR dueDate = SELECTEDVALUE('Table'[Due date]) RETURN IF(MAX('Dates'[Date])<dueDate,"N","Overdue")
And a screenshot showing the end result

Regards,

LC
Interested in Power BI finance templates? Check out my blog at www.finance-bi.com
- 21818
  Frequent Visitor
  6 years ago
  Hi lc_finance and jthomson ,
  
  I managed to solve this using your suggestions.
  
  Many thanks for your help.
  - lc_finance
    Solution Sage
    6 years ago
    glad to hear you found a solution.
    
    Let us know if you need more help,
    
    LC

Forum Discussion