Forum Discussion
Setting up data model
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
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