cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Adapt formula to ignore blank cells

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)

 

Capture.JPG

 

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)

 

Capture2.JPG

 

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

 

Capture3.JPG

 

Thanks! 

 

E

1 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

View solution in original post

2 REPLIES 2
Community Support
Community Support

Hi @ET_Phone2 ,

 

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.

1.PNG

Step 2#

Select 'merged' column and pivot the column.

2.PNG

Result would be shown as below.

3.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

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

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors