Calculated Column with multiple conditions

[TiGGowich]

Hi Everyone,

This is my first post ever and I hope you awesome people will be able to help me out. I have had a look through the forums and I found a few posts related to this topic that got me on the right path, but I tried various things for hours and still couldn't figure it out 100%.

Problem:
I have an XLS file that is used as an action log. Within that file I have one 1 column that contains a due date for a task. Another column contains a closed date that gets filled in when a task is completed. 

I created another column to calculate the status of each task via this formula: 

=IF(N2<>"", "NA", IF(J2<TODAY(), "Overdue", "Not due"))

So if the "closed date" is not blank (=because the task is finished and there is a closed date), it will just say "NA", but if the cell IS blank, the formula will check the "due date" against today's date and return whether it's "overdue" or "not due."

An extra column just calculates how many days (if any) the task is past its due date:

=IF(O2= "NA", 0, IF(O2= "Not due", 0, IF(O2= "Overdue", TODAY()-J2, 0)))

Simple enough until here right? My problem with this way of doing things is this: when I load this XLS into Power BI, I have a filter to quickly show overdue, or open (due) tasks. For this to work though and be accurate, one always has to open the XLS file first so that the task status can update against the "Today" formula.

This is why I wanted to do the same thing using calculated columns in Power BI so that when people open Power BI (without opening XLS prior to that), The same "today" formula will always automatically update against the due date.

So I created this column:

OverdueActions = IF(TODAY() > 'Action Log'[Due Date], DATEDIFF('Action Log'[Due Date], TODAY(), DAY), -1)

which returns positive values for when a due date is greater than today's date, and negative values for when the task is still due. The problem with this calculated column is that it also shows positive values for all the tasks that are past their due date when they already have an entry in the "closed date" column, so I don't want to count these values as "Overdue" (because the tasks have already been closed).

So I tried to calculate another column to account for this, using the first calculated column and came up with this formula:

Overdue Actions 2 = IF(AND('Action Log'[Date Closed] = "", 'Action Log'[Overdue Actions] >= 0), "Overdue", "Not due")

This returns an error saying that DAX cannot compare a Date with a Text which doesn't make sense to me because Date Closed is in format "Date" and Overdue Actions are of type "Whole number." 

Either way it didn't work and I tried two other formulas that returned the same error:

Overdue Actions 2 = IF('Action Log'[Date Closed] = "", IF('Action Log'[Overdue Actions] < 0, "Not due", "Overdue"), "NA")

Overdue Actions 2 = IF('Action Log'[Date Closed] = "" and 'Action Log'[Overdue Actions] >= 0, "Overdue", "Not due")

What am I doing wrong and how can I solve this problem? My goal is to have a filter on my dashboard using the calculated column: OverdueActions = IF(TODAY() > 'Action Log'[Due Date], DATEDIFF('Action Log'[Due Date], TODAY(), DAY), -1) as a base for a second calculated column that will allow me to simply filter my visuals by "Overdue" and "Not due".

Obviously I can make this all work by simply opening the XLS file every time before opening Power BI (so that all entries in XLS will be updated against the "TODAY" function there. But I want to achieve this by using calculated columns in Power BI so that any user just needs to open Power BI and the "TODAY" function will calcuate the overdue status in the background without needing the XLS file.

I hope this convoluted explanation made sense and I appreciate any kind of feedback and solution!

Many Thanks!!!!

[v-yiruan-msft · Accepted solution]

Hi  @TiGGowich ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Please refer the following link to connect the XLS file in Power BI Desktop

Connect to Excel in Power BI Desktop - Power BI | Microsoft Learn

2. Create two calculated columns as below:

IsOverdue = 
IF (
    NOT ( ISBLANK ( 'Action Log'[Date Closed] ) ),
    "NA",
    IF ( 'Action Log'[Due Date] < TODAY (), "Overdue", "Not due" )
)

OverdueActions = 
IF (
    'Action Log'[IsOverdue] IN { "NA", "Not due" },
    0,
    DATEDIFF( 'Action Log'[Due Date], TODAY () ,DAY)
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[TiGGowich]

Thank you 🙂 Did exactly what I needed it to! Thank you very much for your quick help! I accepted it as the solution - greatly appreciated!

[TiGGowich]

Thank you! This looks so simple and logical now that you've done it, and yes it does exactly what I wanted it to do!!

I appreciate your help - that was super quick!

Many Thanks 🙂