Calculate column according to a different date table

[21818]

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 · Accepted solution]

Try pulling through the largest value in your date table as a variable and then compare your due date column to that?

[lc_finance · Accepted solution]

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]

Hi @lc_finance  and @jthomson ,

I managed to solve this using your suggestions. 

Many thanks for your help.

[lc_finance]

glad to hear you found a solution.

Let us know if you need more help,

LC

[jthomson · Accepted solution]

Try pulling through the largest value in your date table as a variable and then compare your due date column to that?