Setting up data model

[Anonymous]

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)

[Greg_Deckler]

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

[Anonymous · Accepted solution]

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