Best Practice Interacting with most Recent Date Data and Historical in the Same Report

[seabrew]

On a report I need to slice and data detailed fact table data for the last month end and have that filtered data interact with a stacked bar chart displaying historical data. I got the data to interact mostly the way I wanted by creating a second fact table based on the first and filtering the date of the second table to last month end. There are still some quirks with different aggregation that I am working through.

I then tried splitting out the fact table on the SQL side into one for historical and one for most recent. I am having less success getting the different dataset visuals to interact the way I had them with my first approach. 

Is there a best practice for using fact tables that look at the different periods in the same Power BI page? Am I totally going about it wrong? 

[v-shex-msft]

Hi @seabrew.

As smoupre said, if you share some sample file, it will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[Greg_Deckler · Accepted solution]

Generally, in a case like that you would create another query for each shared dimension between the tables. You would relate this dimension to each of your fact tables and use this shared dimension within your visuals, slicers, etc. Can't really do much more without examples data and what you are trying to accomplish.

[seabrew]

Thank you @Greg_Deckler! The shared dimension was my issue.

My first attempt had a shared dimension between the fact tables, but my aggregates weren't working correctly. Then I switched to two different dimenions but a shared key between the two fact tables. 

Now I am back to a shared dimension table and I figured out my aggreation issues. I am very comfortable with transactional databases but newer to BI/Analysis Services systems. I have so much more learning to do!