Forum Discussion
Can PowerBI replace my Excel time series data model?
Hi,
I am an avid Excel user, but I am fairly new to PowerBI and its possibilities. I have watched some basic training videos, but before I invest too much time I would love to get some overall feedback on how PowerBI could potentially solve some of my use cases or if it's better to stick with Excel.
One of the use cases I am looking to solve in PowerBI is replicating my "calculation" model in Excel, see below. Obviously, I would have to do many learnings before being able to perfectly replicate the calculation sheet, but there are at least some overall questions I have:
- Will this even be possible? If yes, what is the short answer.
- Is it possible to create a combined (time series) data table in PowerBI where I connect (or relate) in the raw data based on date information? See screenshots with example.
Any help and/or additional pointers would be greatly welcomed 😀
Frank
***
My current Excel model
- Excel model for calculating SaaS metrics over time, i.e. monthly time series data
- Calculations are done in a separate sheet where I typically use Sumifs functions (or similar functions) to bring in data from the relevant data sources and for the applicable time period
- Raw data include ARR/CRM data and financial data. There are no data fields that can be used to build "relationships" between the data sources, except the time period
- See screenshots for how the Excel model works overall (example calculations and raw data).
Calculation sheet (the end result): Example 1 - Getting in ARR data
Summarizing ARR data
Calculation sheet (the end result): Example 2 - Typical calculations performed
Calculations
Raw data: CRM/ARR data (manually imported to a separate sheet from CRM)
Raw data: Financial data (manually imported to a separate sheet from Accounting system)
Hi Anonymous
You can create custom table using DAX. The option available in the modelling tab. To create a date table you need to use calender function. You may search your queries in Google , chat GPT, CoPilot to get relevant codes to built the model.
Hi Anonymous ,
I'm glad you're using an interesting tool.
Here are the common steps for how to create a report:
1.Import Data:
Open Power BI Desktop.
Go to Home > Get Data > More....
Select your data sources (e.g., Dynamics 365 for CRM data and your financial data source).
2.Transform Data:Use the Power Query Editor to clean and transform your data.
Ensure both datasets have a common date field for using.
Use merge/append queries to enhance data associations or make data more concise
3.Create Relationships:Go to the Model view in Power BI.
Ensure the relationship between the CRM data and financial data is correctly established based on the date field.
4.Create Visualizations:Use the merged data to create your desired visualizations and reports.
Reference:
How to Connect CRM to Power BI for Advanced Data Analysis
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous
the short answer is YES. you can relate, append , merge multiple tables in power bi. you can do calculations in power BI using DAX as you do in excel and it more feature rich. some of the dax formulas are very similar to excel formulas as well like "iferror", "sum", "min","max" etc. "Sumif" can be done using "sumx" in DAX.
try it out .Thanks for the quick reply Rupak_bi.
My understanding is that it should be possible, but I am struggling with understanding how I will be able to create a new custom sheet (i.e. data table in PowerBI) and connect/append the CRM data + financial data only based on date information, ref screenshots? Any pointers or links to relevant help documentation you could share?
Thanks again.
Hi Anonymous
You can create custom table using DAX. The option available in the modelling tab. To create a date table you need to use calender function. You may search your queries in Google , chat GPT, CoPilot to get relevant codes to built the model.
Hi Anonymous ,
I'm glad you're using an interesting tool.
Here are the common steps for how to create a report:
1.Import Data:
Open Power BI Desktop.
Go to Home > Get Data > More....
Select your data sources (e.g., Dynamics 365 for CRM data and your financial data source).
2.Transform Data:Use the Power Query Editor to clean and transform your data.
Ensure both datasets have a common date field for using.
Use merge/append queries to enhance data associations or make data more concise
3.Create Relationships:Go to the Model view in Power BI.
Ensure the relationship between the CRM data and financial data is correctly established based on the date field.
4.Create Visualizations:Use the merged data to create your desired visualizations and reports.
Reference:
How to Connect CRM to Power BI for Advanced Data Analysis
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.