Do I really need to maintain two separate Date Tables to achieve this

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…

image

Project Close Date Month Value
Project 1 01/01/2020 01/01/2020 6933
Project 1 01/01/2020 01/02/2020 1478
Project 1 01/01/2020 01/03/2020 7893
Project 1 01/01/2020 01/04/2020 2945
Project 1 01/01/2020 01/05/2020 4931
Project 1 01/01/2020 01/06/2020 9784
Project 1 01/01/2020 01/07/2020 2689
Project 1 01/01/2020 01/08/2020 1427
Project 1 01/01/2020 01/09/2020 2973
Project 1 01/01/2020 01/10/2020 3622
Project 1 01/01/2020 01/11/2020 4405
Project 1 01/01/2020 01/12/2020 5359
Project 2 01/04/2020 01/04/2020 5598
Project 2 01/04/2020 01/05/2020 9513
Project 2 01/04/2020 01/06/2020 1296
Project 2 01/04/2020 01/07/2020 3260
Project 2 01/04/2020 01/08/2020 2226
Project 2 01/04/2020 01/09/2020 9230
Project 2 01/04/2020 01/10/2020 5929
Project 2 01/04/2020 01/11/2020 6597
Project 2 01/04/2020 01/12/2020 6895
Project 2 01/04/2020 01/01/2021 7265
Project 2 01/04/2020 01/02/2021 8878
Project 2 01/04/2020 01/03/2021 4680

I have inactive relationships…

image

Test = CALCULATE (
CALCULATE (
    SUM ( 'Table'[Value] ),
    USERELATIONSHIP ( 'Table'[Month], 'Date'[Date] )
),
CROSSFILTER ( 'Table'[Close Date], 'Date'[Date], NONE )

)

But I cannot get this to display the correct Quarters on the Rows, against the Value Columns

image
Test20201007.pbix (56.4 KB)
Thanks
Nev

Hi @Neville,

Yes ou can just maintain one date table and have

  1. Active relationship between the date table and Closed Date.
  2. 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

Hi @MK3010,

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.

Thanks
Nev

Hi @Heather, thanks for responding.

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.

Thanks
Nev

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.

image
image

Test20201007.pbix (58.6 KB)

Hi @Heather,

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.

Hi @Neville,

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.

Also you can make one relationship as active instead of both inactive.

And if you want to include Column sub total hen you can goto format and then to subtotal

image

And turn on the Per Column level. You can adjust as per your need.
image

@MK3010, but the table is displayed incorrectly.

This is the result I’m after, using a single date table.

image

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.

Thank you all, maybe this is not possible without using two date tables.Test20201007.pbix (81.4 KB)

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.

@BrianJ, Any ideas on this. Can it be done without have two date tables?

Thank you
Neville

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.

Hi @markperrone,

Thanks for responding, looking for any solution that stops the need from having 2 date tables.

Nev

Hello Neville

And in this way how I design the matrix is not a good solution with only two tables and the original measure “Test”?

Test1

Hi Neville,

My solution in this situation is creating the Project table with “Close date” and using USERELATIONSHIP to add the Quater column as below.

When we creating a dimension table, we also change the Data Model a little bit.

In the end, this is my matrix table as you need.

Hope this helps! :slight_smile:

Test20201007.pbix (84.5 KB)

1 Like

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!

Thanks @NhatLam, was hope not to have to increase the size of the table, in creating another column, but looks like DAX cannot handle this.

Thank you everyone that responded, to this challenge.