Forum Discussion
Setting up data model
Hi All! My team is moving some of our legacy SSRS reports to Power BI and I've run into a snag with how to set up the data model for one particular report and was hoping for some help.
We have a report where the user sets the time period they want to review as well as an 'as of' date. So for example, they might set the time period to be from 3/1/2019 to 3/31/2019, and the 'as of' date to be 3/19/2019. When the report is executed, these values are passed to the stored procedure as follows: start_date = 20190301, end_date = 20190331, as_of_date = 20190319.
The subsequent SQL query that gets generated in the stored procedure is similar to below:
select * from fact_table1 where time_period between 'start_date' and 'end_date' and asof_date = 'as_of_date'
union all
select * from fact_table2 where time_period between 'start_date' and 'as_of_date'
The first table contains snapshot 'forecasted' data, and the second table is the actuals data. Incorporating the first table hasn't been an issue as I'm creating 2 'time' dimensions and setting them accordingly to the corresponding dates in the table.
It's the second table that's throwing me off as it does not contain the 'as of' date like the first table, and it is relying on the 'as_of_date' as the 'end_date'.
Any ideas? If I can provide more details, let me know (it's tricky for me to explain)
Thanks Greg. I'm new to Power BI, but super excited by the possibilities. I'll add more context to the below.
Essentially what we're doing is unioning our 'actuals' table and our future 'forecasted' table. Depending on the dates selected, data could come from both tables or just 1.
I created some dummy data that represents the 2 tables we're pulling from.
forecasted table transaction_ID transaction_date as_of_date Revenue 1 3/21/2019 3/18/2019 75 2 3/23/2019 3/18/2019 125 3 3/28/2019 3/18/2019 175 1 3/21/2019 3/19/2019 225 2 3/23/2019 3/19/2019 275 3 3/28/2019 3/19/2019 325 4 3/29/2019 3/19/2019 375 actuals table transaction_ID transaction_date Revenue 1001 3/1/2019 100 1002 3/3/2019 125 1003 3/3/2019 150 1004 3/15/2019 200 1005 3/14/2019 250 1006 3/17/2019 300
In the original example I provided, the user enters a 'start_date' = 3/1/2019, 'end_date' = 3/31/2019, and an 'as_of_date' = 3/19/2019. I highlighted in Red the records that would be selected for this scenario. This would give me a Revenue of $2325.
In this example, we're wanting data from the 'forecasted' table from the 'as_of_date' = 3/19/2019 to the 'end_date' = 3/31/2019, and data from the 'actuals' table from the 'start_date' = 3/1/2019 to the ('as_of_date' - 1) = 3/18/2019.
This is kind of challenging for me to explain in plain english, so if I can provide more clarity, please let me know. Thanks in advance for the help!
**EDIT** Was able to come up with a solution using DAX to make a few intermediate calculations that I think got me what I needed. Closing the thread
2 Replies
This seems very doable but tough to say exactly. Can you provide some sample data and expected output? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thanks Greg. I'm new to Power BI, but super excited by the possibilities. I'll add more context to the below.
Essentially what we're doing is unioning our 'actuals' table and our future 'forecasted' table. Depending on the dates selected, data could come from both tables or just 1.
I created some dummy data that represents the 2 tables we're pulling from.
forecasted table transaction_ID transaction_date as_of_date Revenue 1 3/21/2019 3/18/2019 75 2 3/23/2019 3/18/2019 125 3 3/28/2019 3/18/2019 175 1 3/21/2019 3/19/2019 225 2 3/23/2019 3/19/2019 275 3 3/28/2019 3/19/2019 325 4 3/29/2019 3/19/2019 375 actuals table transaction_ID transaction_date Revenue 1001 3/1/2019 100 1002 3/3/2019 125 1003 3/3/2019 150 1004 3/15/2019 200 1005 3/14/2019 250 1006 3/17/2019 300
In the original example I provided, the user enters a 'start_date' = 3/1/2019, 'end_date' = 3/31/2019, and an 'as_of_date' = 3/19/2019. I highlighted in Red the records that would be selected for this scenario. This would give me a Revenue of $2325.
In this example, we're wanting data from the 'forecasted' table from the 'as_of_date' = 3/19/2019 to the 'end_date' = 3/31/2019, and data from the 'actuals' table from the 'start_date' = 3/1/2019 to the ('as_of_date' - 1) = 3/18/2019.
This is kind of challenging for me to explain in plain english, so if I can provide more clarity, please let me know. Thanks in advance for the help!
**EDIT** Was able to come up with a solution using DAX to make a few intermediate calculations that I think got me what I needed. Closing the thread
