Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SreejithR
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

 Opportunity_KeyOpportunity_Created_DateFiscal_YearFiscal_QuarterFiscal_MonthRain_makerTCV
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

Opportunity_KeyRain_Maker_GIDRain_MakerEffective_Start_DateEffective_End_Date
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

 

 

Sample_Dashboard.png

 

thanks

Sreejith

2 REPLIES 2
lbendlin
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

 

thanks

Sreejith

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.