Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have an Excel data sheet that is set up on a monthly refresh schedule and pulls in aggregate project information, appending it to the previous data set which is then set apart by a Report Date column. This is all in a single table/single sheet feeding into Power BI. For instance:
Project ID | Stage | Report Date |
PRJA | Concept | 4/27/2024 |
PRJB | Plan | 4/27/2024 |
PRJC | Execute | 4/27/2024 |
PRJD | Close | 4/27/2024 |
PRJA | Concept | 5/27/2024 |
PRJB | Execute | 5/27/2024 |
PRJC | Execute | 5/27/2024 |
PRJE | Concept | 5/27/2024 |
I need to create a new table or measure to be able to single-out changes between report dates which are selectable by the user. For instance, this table will grow - so while right now I'll only need to reference changes from April to May, I'll soon need to reference changes from May to June, etc.
In this example, and in reference to just these two dates above, I would need to be able to call out that:
If to draw this information out I need to create multiple measures or multiple tables, that's fine - I just need to be able to weed out the changes against current and future report dates.
Thank you very much in advance! I hope I've explained this well.
Thanks,
Jerry
Please try the approach in the attached pbix.
Proud to be a Super User!
Thank You!
Hi @Mrloler2
Questions:
If it just a simple comparison of the statuses between the current and previous report date, you can try the calculated column below. Please note that PRJD is tagged as added as it doesn't have a prior record.
Proud to be a Super User!
Thank you for the prompt reply. Answers to your questions:
I hope this helps clarify my situation. I essentially need to be able to visualize an answer to the following business questions:
Helping uncover the formula for this will help me answer a number of other time-based analysis questions, but these are the most critical. I'm racking my brain over how to showcase this and just keep hitting dead ends. Not sure if I need to look at a Conditional Column, New Table or New Query to get this detail.
Thanks for your help!
Jerry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
83 | |
76 | |
65 |
User | Count |
---|---|
120 | |
112 | |
94 | |
84 | |
75 |