Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Looking for help - To show historical data conditionally

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

964/10/2019 0:00FY 19-20Q1AprDonald750000
1004/26/2019 0:00FY 19-20Q1AprDonald250000
1725/16/2019 0:00FY 19-20Q1MayJohn500000
1835/16/2019 0:00FY 19-20Q1MayJohn250000
1865/16/2019 0:00FY 19-20Q1MayJohn2200000
5465/14/2020 0:00FY 20-21Q1MaySteve2400000


Rainmaker_Type2 dataset

96212048Donald4/10/2019 0:0012/31/2050 0:00
100212048Donald4/26/2019 0:0012/31/2050 0:00
172372077Sam5/16/2019 0:007/8/2020 0:00
172239839John7/8/2020 0:0012/31/2050 0:00
183372077Sam5/16/2019 0:007/8/2020 0:00
183239839John7/8/2020 0:0012/31/2050 0:00
186372077Sam5/16/2019 0:007/8/2020 0:00
186239839John7/8/2020 0:0012/31/2050 0:00
546671357Steve5/14/2020 0:0012/31/2050 0:00







Super User
Super User

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.

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




Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors