Adapt formula to ignore blank cells

[Anonymous]

Hi all, 

This is a super simple request but for some reason I just can't think of a way of adapting my formula to work without completly re-writing the formula which I'm not happy to do as it took ages to get a formula that worked! 

So I have two files of data with dates to track when some work has been completed and then a corresponding date for this. These tasks need to be re-completed at certain times and I'm trying to track whether these have been done or not in a table with a Current vs Previous column headers. 

This all works fine and to figure out the previous date someone was very greatful to give me the following formula that works a charm:

Previous =

VAR MaxDate = Calculate(MAX('Table1'[Date]), ALL('Table1'[Date]))

VAR PreviousDate = CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < MaxDate)

return IF(ISBLANK(PreviousDate), MaxDate, PreviousDate)

This works a charm because largely if someone has completed a task in the previous month and doesn't need it to be re-completed for a year (e.g) then the date in the current month will be the same but if the dates are different in the two months my table will show the previous date as it should. Now this formula doesn't take into account any new starters who haven't completed training in the previous month because they weren't here but now have this month. 

I've tried to add a filter function in the middle of the PreviousDate formula above to filter out any blank cells before completing the calculation 'Table1[Date] < MaxDate but this seems to be ignored. I've even tried it before the MAX calculation in the PreviousDate function but no luck. 

My Date looks like this (I then pull all the dates into one column in Power Query)

The end result should look like this (note for the 'Current' column I'm just using a normal MAX function so there is no issue there)

What it IS showing at the moment is this: (this is wrong)

Thanks! 

E

[v-jayw-msft]

Hi @Anonymous ,

I'm not good at DAX formula. But I figured out to achieve the result by two simple steps in Query Editor. Hopefully works for you.

Step 1#

Select 'extract date' and 'task name' columns then merge columns.

Step 2#

Select 'merged' column and pivot the column.

Result would be shown as below.

Best Regards,

Jay

[Anonymous · Accepted solution]

Hi Jay @v-jayw-msft 

Thank you very much for responding! My dataset has all tasks as their own column already which I then transpose into one column for ease and this needs to be dynamic. To get around this I created a caculated column in my main table and used the following formula:

Previous Names = IF('Table1'[Names] = RELATED('Table2'[Names]),1,0)

This will then put a zero next to those that weren't here the previous month based on a hierarchy I took from my Excel data through Power Query. I then went ahead to use the same principle when searching for any new tasks that may be new this month.

I then calculated these both columns (Previous Names and Previous Tasks) into one via a measure (INDEX = CALCULATE(SUMX('Table1', 'Table1'[Previous Names] + 'Table1'[Previous Tasks]), FILTER('Table1', 'Table1'[Extract Date] = 'Table2'[Previous Date Measure]))

Finally I went back to the formula I posted in my original message and changed it as such:

Previous =

VAR MaxDate = Calculate(MAX('Table1'[Date]), ALL('Table1'[Date]))

VAR PreviousDate = CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < MaxDate)

return IF(ISBLANK(PreviousDate) && 'Table1'[INDEX] = 2, MaxDate, PreviousDate)

This then gave me the desired result. Next I just need to figure out how to get my conditional formatting colours to work as they are not taking into account the INDEX table. 

Thanks,

E