How to join two virtual tables in DAX

[bonjourposte]

My boss gave me some SQL code to generate a very specific set of filtered information, and I'm translating it into DAX.  We need to create two virtual tables (T1 and T2) out of one (LOANHIST).  

This will tell us if loans that were a certain status in timeframe 1 (T1) turn into a different status in timeframe 2 (T2).  

I've created the two tables, and written out my filters.  Here are the two virtual tables:

And here are our filters:

The error message I'm getting is:

Suggestions?

[MattAllington]

I'm going to share some advice which you can take onboard, or not. DAX and Power BI are not the same as SQL. What you have described here is a SQL solution to a SQL problem. In Power BI, the approach is different. You should aim to build a semantic data model containing tables of data in a simplified schema optimised for reporting. Typically this is a star schema, but sometimes it can be a bit more complex.  Then you would use the visualisation UI to build the report you need. There is no need to write a query to get the result; you use the visuals to create the result structure and measures to calculate the results. Exactly how to do this depends on the model. 

if I were doing this, assuming the model is fit for purpose, I would write 2 measures independently of each other and add them to a table to show a list of (customers?) that have that status for each period, and display both in the table. Then write a third measure that compares the first 2 measure to get the result you need. 

[bonjourposte]

Thanks yeah, I was hoping I'd get a response like this.  I wasn't sure if all this code was necessary, but the dates of each table will need to be updated on a monthly basis.  I've built the model in Power Query as you said and proved that it works when I type in the dates manually, but I want it to generate the data on its own every month.... like I want it to acknolwedge two parameters: the current month and the previous month... every month.  Is that DATEADD?  Any other considerations?

P.S. I added to my question a bit.

[MattAllington]

I suggest the following

load all the data; do not filter by date in power query

Add a calendar if you haven't already https://exceleratorbi.com.au/power-bi-calendar-tables/

if your calculations are always current month, previous month, then you can use a month column in your calendar table to select any month, write a measure for the selected month (selected via a slicer, for example), and write anothe measure that time shifts for the previous month. Yes, you can use DATEADD to do that time shift. An overview of time intelligence is available here.

https://exceleratorbi.com.au/dax-time-intelligence-beginners/

[bonjourposte]

Hi Matt,

I did as you said- I copied the LOANHIST table twice and labeled them T1RE and T2RE.  Then i merged them based on the Loan #.  

This DAX...: 

...successfully gives me this table...:

...if I filter the tables in Power Query by Accounting Date:

01/31/2019 for T1 and

02/28/2019 for T2.

The tables are both connected to the date calendar: 

How would I modify the DAX to be able to 1) unfilter the tables in PQ, and 2) fill in a table like this: 

This table is the goal.  

I know CALCULATE must be involved, but I don't know how to structure the formula.

[MattAllington · Accepted solution]

You don't need to/shouldn't copy the table twice. You should load the table once containing all the data. All of it, not just the 2 months you are interested in, but all history you may ever need for reporting. Then join to a calendar table containing all dates for the superset of dates including dates with no sales.