Create scorecards by linking statues to date

[Anonymous]

Dear all
I have two reports; 
- One with dates (Start date and completion date)

- the other with completion statues (Complete and Not Complete)

The two tables don't have any relation, but I want to create scorecards that show the number of people who have completed a certain program and the other that shows the number who did not complete. The cells in the completion date column that are empty or in the future  are "Not Complete", while those that have dates in them that are in the past are "Complete"
Below is a sample of the excel files
Thanks in advance

https://drive.google.com/drive/folders/1W8H-9CCp-D9T6i3VRRUr0n_Jh51-JD2I?usp=sharing

[Sumanth_23]

hi @Anonymous - The approach explained by @Thejeswar would be the way to go if your data is setup as per the files shared. And yes, you will have to keep updating the calculated column with the new logic and the corresponding statuses if there are additional statuses required. 

Another approach would be to capture the status flag (1, 2, 3, etc.) along with the dates and maintain a DIMENSION table for Status code and corresponding Status description - and lookup each of the Start and End date combinations to derive the status from the DIMENSION table. 

Do vote/mark this post as a solution and provide kudos if it helped you with your question. This would also help others on the community who may have similar questions. Thanks! 

[Thejeswar · Accepted solution]

Hi @Anonymous ,

You can create a new column in the Data set where you dates using the below DAX as  a calculated column

Status = 
VAR LatestStatus = IF(Dates[Completion Date] >= TODAY() || Dates[Completion Date] == BLANK(), "Not Completed", "Completed")
return
LatestStatus

Use the New Status column in both the Axis

In one axis as a category and in the other with a count. You should get a sorecard. PFB image for the same

[Anonymous]

@Thejeswar first of, thank you for your response. If the status increase in the future say from 2 to 30 for example "Complete" "Not completed" "Pending" "Suspended" "Abandoned" ... and so on do I have to manually type all the 30 words into the DAX code? 
I did not see you use the status table

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure whether I understood your file correctly, but when I opened your excel file, I saw one line that showed start date = 6/19/2017 and completion date = 3/27/2013

Please check and please help me to understand more about your data.

Thank you.

[Anonymous]

@Jihwan_Kim that was an error. I u can randomly generate any date. The completion date must be after the start date
Thanks