My FACT Table as two Dates, Closed Date and Revenue Date with a Value.
I’m looking to show the Revenue in the Columns against the Closed Date in the Rows, and currently using two date tables to achieve this. I’m sure there is a better way…
Active relationship between the date table and Closed Date.
Inactive relationship between the date table and Revenue Date.
The active relationship will work as normal but for inactive relationship you need to use USERELATIONSHIP function. Please refer below discussion which has good links describing how to achieve it.
Can you attach your data model? (or a slimed down version of it)
I’m wondering if this would work:
link the date table to one of the date fields in the data table (for this example we’ll say it’s the close date)
then for the “Month” (I’m assuming this is your Revenue date), add one or two columns to allow you to bucket the data by year and quarter
so for the Closed Date (columns) you’d bring over the date table data, and for the Revenue (rows) you’d bring over the date fields added to the data table
now, if your data table is very large, this may not be a good idea
Thanks for responding, but unfortunately I posted the question before completing it. Can you take a look again, as I’ve already done what you’ve said, but the matrix is not come how I want it.
I’ve attached a slim down version, and yes my Date table will be quite large which is why I’m trying to get to one date tables, so I can have a Matrix similar to the Pivot Table image from Excel.
it’s actually the data table that I’m concerned with - if you have a lot of data, then adding the two columns that I’m suggesting may cause a performance issue. Please take a look at the attached.
I have added quarter and year information to your data table, so that you can pull the row fields from a different source from the column fields. Unfortunately, an inactive relationship isn’t going to change the way the table filters itself - which is what you had going on with your first example.
Yes, I want to avoid having to do this as a solution, and use the Date Table and DAX to get the Matrix visual to show Quarters of OI Close Date on the Rows, and Years, Quarters, Months for the Value / Month Column.
Currently I use two separate date tables to achieve this, which is not ideal and hence this post.
If you try to place the same header in Row and also in Column field then we won’t be able to achieve your result( AFAIK) but if you try to use different header in column field then you can achieve. Please try to have setting as below.
This is the result I’m after, using a single date table.
What I’m looking forward is a solution that provides the same results, but only using a single date table. I’ve updated the pbix to show the model that I use today.
Well, the only other recommendation I can make is that you slim down the ‘other’ date table as much as possible, remove any columns that you won’t need in the output for this (and any other) visual that uses both date fields. Not ideal, but it at least will reduce the file size somewhat.
We just did this with a monthly summary by complete date when the default relationship with the calendar was based on create date. We had USERELATIONSHIP. I’m taking a look at your example and attach it to my response. The other possibility is a canoncial date - a little more complicated for the model but here’s the video https://www.youtube.com/watch?v=8vfzcd6rIRw which shows how to do this with Qlik.
A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
