Calculate status based on dynamic dates

[matheus_]

Hi

I'm new to PBI and I'm struggling to find a solution for my problem.

The problem

I need to calculate (count) the statuses of my data based on specific dates (e.g. weekly on Wednesday).

Actions	Deadline	Start Date	Finish Date	Status
Action 1				f(Reference Date, Deadline, Start Date, Finish Date)
Action 2

Describing my data (fActionPlan)

• The Status might be signaled as to do, ongoing, delayed, concluded, cancelled.
• The Status is defined with logic comparison of the fields Deadline, Start Date, Finish Date, while also might change depending on the Reference Date I'm considering. Example
  
  • An action with Deadline = May 18 and Finish Date = May 25 should be accounted as "Delayed" from the Start Date up to May 24, and it should account as "Concluded" from May 25 and beyond.
• Actions are unique entries.

The expected outcome

• A stacked bar chart with the status count in multiple dates.

Any guidance on this?

Thank you for any advice.

[v-alq-msft]

Hi, @matheus_ 

Based on your the logic of the calculated column, I created sample data to reproduce your scenario.

There is something wrong with the result because '_finishdate>_refdate' appears in each branch. Could you please show us some sample data and expected result with OneDrive for Business? Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[matheus_]

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?

[matheus_]

Hi

@v-alq-msft @amitchandak do you believe this is something doable with DAX/PowerBI? 

Would it be easier with Python/R scripts? Maybe a different way to structure my data?

Thanks for any guidance you may have.

[amitchandak]

@matheus_ ,Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

You need a new column like

if(datediff([start date], [finish date] ,Day)>=7, "Delayed","Not Delayed")

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.

[matheus_]

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?