Completed date for Projects

[aymankh]

Hi,

For the table (dummy values) shown below 

I am trying to create a pie chart for a list of projects which are in progress or completed. I am facing an issue in doing the same since I want the pie chart for the current date, however the issue is there are projects which have been in progress since last year and completed recently which do not show up in the pie chart.

If I put a start date filter > 2021 it will only  show completed projects and in progress after 2021 and If I put the completed filter> 2021 , it will only show the completed projects. If I remove the filter it will show me all the projects which got completed in 2021 and 2022 as well as the in progress projects.

Can some one guide how I can create a filter or query which only shows the current-in progress projects and the current completed projects (which may have started anytime before 2022). 

I hope the query is clear to be answered.  

Example 

Project	Start date	Completed date	Status
Project A	2021-08-21	2022-03-24	Completed
Project B	2021-08-23		In-Progress
Project C	2021-08-22	2021-08-25	Completed
Project D	2022-01-20	2022-03-24	Completed
Project E	2022-03-22		In-Progress

[v-eqin-msft · Accepted solution]

Hi @aymankh ,

Based on this——how I can create a filter or query which only shows the current-in progress projects and the current completed projects (which may have started anytime before 2022). 

Let's assume the "current-in" means the current month( March 2022), so for the data you provided, A, D, E should be shown in visual, right?

Please try to create a flag measure:

Flag = 
var _startDiff=DATEDIFF(MAX('Table'[Start date ]),TODAY(),MONTH)
var _compDiff=DATEDIFF(MAX('Table'[Completed date]),TODAY(),MONTH)
return IF(_startDiff=0 && MAX('Table'[Completed date])=BLANK() || MAX('Table'[Completed date])<>BLANK()&& _compDiff=0 ,1,0)

And then apply it to visual-filter pane, below is the output:

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

[aymankh]

Hi Eyelyn9,

Thank you for the detailed visual , let me try and see if this works and respond back on this . This should capture all in progress projects as well as projects which were assigned last year but got completed for this month say March which you dod correctly point out 

[aymankh]

Hi Vijay , I have already tried doing so , however doing so will still give the completed projects which have completed last year , in this table Project C (2021-08-25) which I do want. I only want the pie chart to show the all projects which are in progress and recently completed (Project A and Project D). 

[Vijay_A_Verma]

In a query step, use the below (Replace #"Changed Type" with your previous step)

=Table.SelectRows(#"Changed Type", each Date.IsInCurrentYear([Completed date]) or [Completed date]="" or [Completed date]=null)

[Vijay_A_Verma]

I believe Status column is already giving this info. If this column is not there-

Apply filter on Completed as Non Blanks - Completed

Apply filter on Completed as Blanks - In progress