Tiered Formula for Dates

[DominicB1990]

Hi,

I am working within Power BI on some visuals for a portfolio of buildings as to when their Building Warrant of Fitnesses are due (which is an annual anniversary date). There might be an easier way but i'm trying to automatically colour code the sites on the Power BI map into 3 different categories, depending on today's date. I'm trying to do this within the original Excel spreadsheet with all of the data on. I plan to do this by having a separate column with a formula to categorise the time between today's date and the Anniversary date into 3 'statuses'.

For example, if a BWOF is due on 1st January each year. There needs to be an annual survey done on this site between 3-6 months before this due date (I have a separate column on my SS which specifies this timeframe for each site, it's 3 months in this case). What I want to do is have 3 'Statuses' for these anniversary dates, depending on todays date.

Status 1 - 'Compliant' which is between 1st January and 9th October (9th Oct being 3 months before the following anniversary date, when the annual survey is due to be done)

Status 2 - 'Annual Survey Due' which would be between 9th October and 1st January

Status 3 - 'Overdue' which would be if the todays date goes past 1st Jan and there hasnt been a new BWOF issue date put into the next column. Hence meaning the BWOF hasn't been issued so therefore it is past it's due date.

I'm a bit of a novice and i'm really trying to be clear with what i'm requiring but it's pretty difficult when I don't know the correct terminology.

Here's what I have so far which isn't working for me, however, might give you a better idea of what I mean:

What I want:

Status 1 'Compliant' = If todays date is between 01/01/19 and less than the below date, show as 'Compliant'

Status 2 'Annual Survey Due' = If today is within (G2 mins (L2*30days)) show as 'Annual Survey Due'

Status 3 'Overdue' =Is I2 is blank and todays date is passed 1st Jan 2020, show as 'Overdue'

Any help would be greatly appreciate and happy to clarify any of my babble above if it helps.

Cheers,

Dom

[#2]

Hi @DominicB1990

Could you attach a simplified sample file

When you say "today's date" is less than below date, what do you mean by "today"  as it will always be after 01/01/19 and also what do you mean by below date?

Cheers

Wyn

[#3]

@Wyn Hopkins Morning Wyn,

I've stripped out everything and attached a sample.

So 'Today' would always be the date that i'm working on the document and i'm always going to be working on the following year as in 01/01/2020. So if I was working on the document on 01/02/2020, the status would be 'Compliant'. If I was working on it on 25/10/2020, it would be 'Annual Survey Due'.

By date below, whatever the answer to Status 2 is (in this case 1st January 2020 - 90 days is 3rd October) Therefore between 1st January 2020 and 3rd October 2020, the status would be 'Compliant'. Between 4th October 2020 and 1st January 2021, status would be 'Annual Survey Due'. If a date hasn't been added into BWOF Issued (column I) and the date goes past 1st January 2021, the status would change to 'Overdue'.

I understand 90 days less than 1st January 2020 is back into 2019, so to calculate it to 2020 it may be easier to do a different formula to plus the days but I essentially just need the status to change 'Column L' months before the following anniversary date.

Cheers,

Dom

[#4 · Accepted solution]

@DominicB1990 

Try out the attached to see if it helps

[#5]

That seems to be the one, i'm not able to put it all into BI yet, as i'm away but really appreciate your help!

[#6]

How to know scroll lock is ativated?

[#7]

Hi @Wyn Hopkins,

I've just started working on this again and the only problem i'm having is, when I issue a BWOF and put a date into 'BWOF Issue Date' column, I would like it to change the Status back to compliant as the BWOF isn't due again for another 12 months to the cycle starts again.

Any ideas?

Much appreciated,

Dom