Solved: Dynamic Slicer to compare two different periods

MagnaLex · ‎01-03-2024

I have 2 tables. The 1st table is the Opportunity table which contains all the opportunities with the latest information. The 2nd table is a Snapshot table that contains snapshots of the Opportunity table throughout the year.

Both tables are connected through Opportunity ID (Many to 1 relationship). Different fields such as total amount, status, close date can change throughout the year. Another thing to point out is that the opportunities go by the close date. So, if the close date is in December, then that opportunity belongs to December.

I am trying to see how the Q4 2023 opportunity funnel looks like at the beginning of Q4 (10/1/2023) compared to how it is today. To do this, I just go to the Snapshot table and manually filtered out all the opportunities that were captured on 10/1/2023 based on the Snapshot Date field. After that, I can just merge it with the live table. Then, there are the old and new fields for total amount, status, close date, etc. I can use those to assign a new status to each of these opportunities and breakdown how the opportunity funnel used to be and how it is currently. Here are a few statuses:

• Moved Out: Close Date used to be in Q4, but no longer in Q4.
• Pulled In: Previously didn’t exist or not in Q4, now it is in Q4.
• Lost from Funnel: Opportunity’s stage changed to Lost.
• Won from Funnel: Opportunity’s stage changed to Lost.

With the above method, it is very static. I can only compare a specific quarter with the current live data. I was asked to create a dynamic slicer in Power BI to compare different periods. A slicer that switches between Q4 to Q3 to Q2 if needed, so that it compare the data from different periods to the current one. It seems to me that the challenge is with many fields including the "status" is very static, which make it difficult to work with this kind of slicer.

Any thoughts on how I can proceed?

Thank you in advance!