Looking for help - To show historical data conditionally

[SreejithR]

Hello everyone, 

I am new to Power BI and Dax, have an issue which looked simple initially but unable to crack it.
I have opportunities datasets as below and a sample report as below.
You can see in Rainmaker_Type2 dataset, some of the opportunities has changed its assigned rainmakers over time.
My requirement is, when my below dashboard loads initially with a whole year view, opportunities should show the latest rainmakers
Whereas if user clicks on "May" month in visual2 , the first visual should show the may month opportunities with rainmakers assigned at that point of time.
Eg: On initial load, John to be shown against opportunity 172 where as if user clicks on May or Q1 (April/May/Jun) Sam should be shown against 172.
As this rainmaker selection is dynamic depends on timeperiod selection, I am struggling, have tried with calculated measures/columns, but did not worked.
Can anyone please guide me to remove this issue?

Opportunities Dataset

Opportunity_Key	Opportunity_Created_Date	Fiscal_Year	Fiscal_Quarter	Fiscal_Month	Rain_maker	TCV
96	4/10/2019 0:00	FY 19-20	Q1	Apr	Donald	750000
100	4/26/2019 0:00	FY 19-20	Q1	Apr	Donald	250000
172	5/16/2019 0:00	FY 19-20	Q1	May	John	500000
183	5/16/2019 0:00	FY 19-20	Q1	May	John	250000
186	5/16/2019 0:00	FY 19-20	Q1	May	John	2200000
546	5/14/2020 0:00	FY 20-21	Q1	May	Steve	2400000

Rainmaker_Type2 dataset

Opportunity_Key	Rain_Maker_GID	Rain_Maker	Effective_Start_Date	Effective_End_Date
96	212048	Donald	4/10/2019 0:00	12/31/2050 0:00
100	212048	Donald	4/26/2019 0:00	12/31/2050 0:00
172	372077	Sam	5/16/2019 0:00	7/8/2020 0:00
172	239839	John	7/8/2020 0:00	12/31/2050 0:00
183	372077	Sam	5/16/2019 0:00	7/8/2020 0:00
183	239839	John	7/8/2020 0:00	12/31/2050 0:00
186	372077	Sam	5/16/2019 0:00	7/8/2020 0:00
186	239839	John	7/8/2020 0:00	12/31/2050 0:00
546	671357	Steve	5/14/2020 0:00	12/31/2050 0:00

thanks

Sreejith

[lbendlin]

1. clean up your Opportunities table. None of these columns : Fiscal Year, Fiscal Quarter, Fiscal Month, Rainmaker  should be in that table

2. Why should Sam be shown for 172 when May is selected?  What May? May 1st, May 31st, "sometime in May" ?

3. Please do not use moondates. They will kill your calendar's performance. instead of 2050-12-31 use a date like 2020-12-31 or the end of your fiscal year

Your answer to 2. and the assumed presence of a calendar table will then impact the possible solutions.

[SreejithR]

Hi Ibendlin,

Thank you for your reply.

1. Opportunity dataset is currently designed as a ROFT (report over flat table). All the possible columns required for dashboards are included in it in order to avoid further joins and various measures included in it with the help of Union queries. 

2. Sam was the person who created opportunity in the month of "May" and then left the company. The opportunity later assigned to John on August. So on an yearly view John should be shown where as on a quaterly/monthly view Sam to be shown. The lowest level of granularity and clickability is month, So Sam should be shown when curresponding mothname or quarter name selected.

3. Ok

There is no seperate calendar table designed currently as the use case was only to handle change in rainmaker, not as a complete historical reporting

thanks

Sreejith