Forum Discussion
Helper VHow 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'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.
bonjourposteThanks 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.