Get Previous and Next Dates for a given date and determine project Status

[naidu_sandhya]

I have a table with a column of bi-weekly dates to Refresh dashboard. (Thanks to this forum! I could lookup and create this table). I have another table which has the Status Updated Date for an Initiative. As an output, on any day, I need to get the previous and next refresh date closest for that day. Then based on the status updated date, determine if project status is Good/Due/Past Due.

Refresh Table - 

Status Updated Table - 

Output - As of today - 08/13/2022, I want to display the below.

That is -

"Good", if the Status Updated Date is greater than the previous refresh data and lesser than the next refresh date

"Due", if the Status Updated Date is Due before next refresh date

"Past Due", if the Status Updated Date is older than 2 previous refresh dates

My problem is

1. I am not able to get the Previous Refresh Date and Next Refresh Date from the Refresh Table as of today (8/13/2022)

2. How to determine the Project Status as Good / Due / Past Due ?

Any help is much appreciated!

Thanks!

[daXtreme · Accepted solution]

Hi @naidu_sandhya 

Here's a solution. But the logic you gave... well, you have to revise it as it appears a bit flawed. The link is to a pbix file on my OneDrive. Please download the file and inspect. You should be able to adjust it to your liking.

[v-cgao-msft · Accepted solution]

Hi @naidu_sandhya ,

Please try these measures:

Prvious refresh date = 
CALCULATE (
    MAX ( 'RefreshTable'[Refresh Date] ),
    'RefreshTable'[Refresh Date] < TODAY ()
)

Next refresh date = 
CALCULATE (
    MIN ( 'RefreshTable'[Refresh Date] ),
    'RefreshTable'[Refresh Date] >= TODAY ()
)

Project Status = 
VAR _statusupdated =
    MAX ( 'StatusUpdatedTable'[Status Updated] )
VAR _previousrefreshdate = [Prvious refresh date]
VAR _2previousrefreshdate =
    CALCULATE (
        MAX ( 'RefreshTable'[Refresh Date] ),
        'RefreshTable'[Refresh Date] < _previousrefreshdate
    )
VAR _status =
    SWITCH (
        TRUE (),
        _statusupdated >= [Prvious refresh date]
            && _statusupdated <= [Next refresh date], "Good",
        _statusupdated < [Prvious refresh date]
            && _statusupdated >= _2previousrefreshdate, "Due",
        _statusupdated < _2previousrefreshdate, "Past Due"
    )
RETURN
    _status

The PBIX file is attached for reference:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[daXtreme · Accepted solution]

Hi @naidu_sandhya 

Here's a solution. But the logic you gave... well, you have to revise it as it appears a bit flawed. The link is to a pbix file on my OneDrive. Please download the file and inspect. You should be able to adjust it to your liking.

[naidu_sandhya]

Thank you very much!! I did not expect a response this quick!! 🙂 

I might not have written the logic exactly right. Please correct me where is the flaw? 

What you gave me works fine infact. I have just a followup question. Imagine if today was 8/1/2022, is it possible to still show the previous refresh date as 7/18/2022 and next refresh date is 8/1/2022 as the refresh cycle ends by EOD of 8/1/2022 ?