Forum Discussion
How to join two virtual tables in DAX
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.
Community ChampionI 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/
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
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.