Status Over Time Visualization, Detecting Conditionally Distinct Entries, "As Of" Filter, etc.

[LaelTejo]

Hello!

I am still new to PowerBI, and currently exploring the possibilities. I am currently working on a certain report to be updated to my higher ups, and I think using PowerBI would help in processing and handling the process. The report is about the Project Updates of the Project Managers that sends their reports to me. However, there are certain points that I lack the knowledge to execute properly.

I have make it so that my Source Data includes the following informations:

1. A "Reporting Month" column to indicate the month when I received the document. It ranges from January - December. When entering a new month, I will have similar data with a different "Reporting Month".

2. A "Project Code" column to signify the Projects that are being reported uniquely. I need it to be linked with the "Project Name" column.

3. A "Project Status" column to show the progress of said project at the give time. Generally, a project will not have the same status in different month, though some major ones may progress slower.

My goal is to create a 4-page Report: Home, Highlights (How many deployed, etc.), Summarize (What is the projects of Certain PM and how much the budget), and provide Details. Unfortunately, I have no idea how to make the following contents:

1. A "Status Movement" Visualization in the Details Page. I will have a single-selection filter in the Details Page to show a specific project, and I would like to make a visualization that shows what is its status in January, February, March, etc.

2. A "New Project" Information in the Highlights Page. I would like to have a card that shows how many project was not in the list on March, but is on April.

3. An "As Of" filter in the Home Page. I will have a single-selection filter in the Home Page to show the month the report is in. I want ALL results in all the other Pages to follow this filter. I want to make it so that if I select "June", the content will show the update from January - June Reporting Month, and not just the June update. The shown data will be distinct, so the project with the exact same "Project Code" will only count as 1, and any sum card I made only calculate it once.

I hope I managed to make my goals clear. Please help me on things that I wish to create, but don't know how. Considering my unfamiliarity, please do inform me if any of my requirements are better handled in different program/microsoft product.

Thank you in Advance!

[TobyNye]

Does the source data have a full date column or not? Also, is it a one off report or will it continue to be used? If it has a date field you should be able to pass that into the measures I put in my first response and that should get you most of the way there. If not, I'd create a column called month no. i.e. if(month = "Jan", 1 etc.) then concat that with a year column so you'd have Year - Month. Then we should be able to create something similar to the above but a bit different to achieve the same result. Let me know though as the approches will be a bit different depending. On your other point, yes should be able to create something like that, would just need to know how you distinguish a "cancelled" project from one that finished.

[LaelTejo]

I tried, and it worked!

Took a bit more time experimenting on my part but the core idea you presented worked wonders. It is a continuously used data, and I DO need to convert the text to an actual date.

Took me a bit more time for the CANCELLED status, but I made a workaround by. . . Not using PowerBI at all. Instead of having a method to detect if a certain project exist last months but is no longer existing, I just asked my PMs to not delete the project from their documents and instead change the status to DROP. From there, all I had to do was to detect if it is the 1st DROP or subsequent DROP.

Thank you for your response!

[TobyNye · Accepted solution]

1) Easiest thing to do would be to use custom labels. If you create a measure Dummy = 1 (just so it will show on the line graph) then put it onto the y-axis of a line graph with your reporting month on the x-axis. Go into the visualisations pane; Data Labels; then to values: 

Turn custom labels on (if this option is greyed out, add a second measure to the graph Dummy 2 = blank()) this will not add another line to the graph as the value is blank and you should now see the custom labels option is no longer greyed out (make sure you apply the custom label to the correct series! in this case, Dummy). In the custom labels field use a measure Project Status = SELECTEDVALUE('YourTable'[YourProjectStatusColumn]) this will then display the status over each point in the graph, you can play around with the formatting.
2) A bit more tricky but try the following:
create a column in your main projects table called First Date Check =
VAR _first_date = MINX(SUMMARIZE(ALL('ProjectsTable'), [Project Name Column]), MIN('YourDateTable'[YourDateColumn]))
RETURN 
IF([YourDateColumn] = _first_date, TRUE(), FALSE())

This should return the very first date that a project was present in your table for on every row that the project is on.
Next just create the following measure:
New Project = CALCULATE(DISTINCTCOUNT([ProjectName]), [First Date Check] = TRUE())

3) Nice and easy one, if you copy paste your slicer onto several pages you will see a pop-up each time asking if you want to sync the slicers, select ok and then when you change one of the slicer values, it will also change the selection on all synced pages. You can also access the slicer sync window if you go to view in the top ribbon then you will see 'Sync Slicers':

[LaelTejo]

Thank you for your response!

I managed to build no. 1 and 3 with your advice and some other resources I found

However, for part 2, I haven't managed to make that 2nd requirement, which shows the data including from Jan-Jun when selecting Jun. My source data have the Month as TEXT. How would I go about doing it? Also, would your method usable if I wish to create a "Cancelled" Project, as in has an ongoing status in the previous month but is now DROP in the current month?