Calculate status based on dynamic dates

Hi amitchandak thanks for your response.

I'll try to prepare a sample data.

1. What I accomplished and my solution so far

• After your suggestion to use calculated columns, I managed to achieve better results. 
• Before that, I was attempting mostly with calculated measures.
• My current results seem close to what I need.
• I created a calculated column called _Status Dynamic

_Status Dynamic = 

VAR _refdate = fActions[_Ref Date]
VAR _deadline = fActions[Deadline]
VAR _startdate = fActions[Start Date]
VAR _finishdate = fActions[Finish Date]
VAR _flag = fActions[Flag Cancelled]

RETURN

    IF(_flag = "1", "Cancelled",            

    IF(_deadline = BLANK(), 
       "To do",

    IF(_deadline >= _refdate &&             // deadline in the future
       _startdate = BLANK() ||              // no initiated OR
       _startdate > _refdate &&             // initiated in the future
       _finishdate = BLANK() ||             // no finished OR 
       _finishdate > _refdate,              // finished in the future
       "To do", 

    IF(_deadline < _refdate &&              // deadline in the past
       _finishdate = BLANK() ||             // not finished OR
       _finishdate > _refdate,              // finished in the future
       "Delayed", 

    IF(_deadline >= _refdate &&             // deadline in the future
       _startdate <> BLANK() &&             // initiated
       _startdate <= _refdate &&            // initiated in the past
       _finishdate = BLANK() ||             // not finished OR 
       _finishdate > _refdate,              // finished in the future
       "Ongoing", 

    IF(_startdate <> BLANK() &&             // initiated
       _startdate <= _refdate &&            // initiated in the past OR now
       _finishdate <> BLANK() &&            // finished
       _finishdate <= _refdate,             // finished in the pas OR now
       "Done", 
       
       "Review"
        
    ))))))

2. The outcome

• With this calculation, I can manually define a reference date, and get the status count on a stacked bar chart for that reference date. Numbers are matching.
• The problem is that I need to have multiple reference dates, with a stacked bar for each, and the status count for past dates should be immutable.
  • Example: an action signaled as "to do" last week should be accounted as Status = "To do" on May 20.
  • This same action, if finished on May 26, should be accounted as Status = "Done" on May 27.

Suggestions?

Hi v-alq-msft

I prepared a sample data but then found out I'm not able to share links externally.

From your image, I can suggest the correction:

• _Ref Date is not a column in my dataset. It should work as constants over time. 
• During validation steps, the deadline will be always inputted manually if the finish date is defined, so the status is likely to be "Done"

Sample data

The expected outcome

The tricky issue here is that:

• I have unique action entries.
• One calculated column for the status category. 
• I need a fixed status count for the past weeks (i.e. from Apr 8 up to May 20).
• While the last status count (May 27) can simply count the status of that date.
• On the x-axis, on May 20, anything started or finished from May 21 and beyond should account as "To do" because I'm looking from the perspective of the past (ref date = May 20).
• On May 27, an action could be set as "Done" as the same entry should be considered "Delayed" on May 20.

Sometimes I suspect such intelligence is not yet available unless I try to model with scripting. Do you have the same opinion?