Multiple Date table

Hello,

I was able to get your assistance to solve an issue with my model two weeks ago. This is my second turn. Presently, I am using two date tables in my model. Initially, thought this was the easy and probably the best way to set up the model to get the result I want. However, I am running into issues when I am trying to bringing other measures into the picture. Other measures use the primary date table.

Anyways, here is the description of the business process. Attached the PBIX files, the question relates to the “changes future” tab. The table shows the output that i am looking to achieve using a single date table.
a) The sales table has two date columns - reading and revenue date. Reading date refers to the date the files was generated and the revenue date refers to the date the revenue will be recorded.
b) The current table shows the rooms sales recorded at each interval in the past for a future date. This is essentially showing the progression of rooms sales from past dates for the revenue dates in the future. Example - assuming today’s date is 6/5/2020, the record shows that we have 14 rooms sold for the revenue date of 7/1/2020 and furthermore, 3 days ago ( 6/5/2020 - 3 days = 6/2/2020), the company had recorded 15 rooms. In addition, 21 days ago for the revenue date of 7/1/2020, the company had sold 23 rooms.

How can I replicate these measures using the primary date table. Is the second date table a must in this model? I did try “use relationship” in the calculate function, but I could not replicate the same result. Revenue.pbix (1.2 MB)

Thanks in advance

I believe most robust way would be to use a single date table. It might be one time effort but has lot of advantages. I think you might have got a great date query written by Malisa in Enterprise DNA that’s amazing, alternatively you can use calendar auto.

If you dont want to use a single date table, you can build an inactive 1-1 relationship ( this might create some errors) and then use relationship.

Regards,

Kamal

Hi @iqbal.

Here’s a link to Kamal’s reference of @Melissa’s extended date table.

Hope it helps.
Greg

Thanks for your response. My model’s date table is as extensive as the one that is being provided in the article. However, I am struggling to write the Dax measure to use a single date table and replace the existing measures in the table. The measures in the table located under the visualization tab “Changes- Future”, is written with the aid of a second date table. Appreciate any help.

Hi @iqbal,

To calculate those measures with one Date table you have to create inactive relationships.

And activate the one you need within each Measure, for example:

-7 days = 
CALCULATE(SUM('Sales_Revenue Fact sheet'[Rooms Sold]),
    USERELATIONSHIP('Calendar'[Date], 'Sales_Revenue Fact sheet'[Reading Date] ),
    DATEADD('Calendar'[Date],-7,DAY))

.

With this result
image
.

I hope this is helpful. Here’s my sample file.
eDNA - Revenue multiple Date tables.pbix (1.2 MB)

.
BTW you might want to explore the last Business Intelligence Summit and use the Search option to find out more about CALCULATE, USERELATIONSHIP, TREATAS and VALUES.

Melissa,

This is great! Thank you for your feedback. The model still have an active relationship to the 2nd date table and once I remove it, all measures with “userelationship” does not work. I also viewed “thinking analytically and developing effectively”, this is really good content. Unfortunately, I could not find any examples that come closer to as a reference point to the business problems that I am working on.

Thanks in advance
Iqbal

Is your goal to get rid of that 2nd date table? Because you are now using it to zoom in to a specific date range. We only needed to create inactive relationships on the first date table to make both dates equal.

As I am learning power BI, initially , I thought it was the easy way handle the functions by way of creating a second date table. However, I am figuring out by doing this way, I cannot bring other measures using the main date table into same table with the measures using the second date table. So to avoid the confusion and help with branching out in measurements, I think its best to use one date table. This is where I am stuck in my model. Does this mean i have no choice but to use the second date table? Application of userelationship appears straight forward, however the confusion is when the two date columns need to be used twice in the same calculation, assuming we need to activate and deactivate the two date columns. Please advise.

Thank you
Iqbal

Okay so here’s what I did.
I made copies of your Fact and Date table and build a second model, on the right.

.
As you can see there is no context coming from the Calendar[Date], so to simulate that date selection I’ve hard coded the same Date value for now but you can replace that with TODAY() or use a hidden Date table to allow for harvesting a Date selection for example, with this basic pattern.

Today v2 = 
VAR cDate = DATE(2020, 6, 5) //TODAY()
RETURN

CALCULATE( SUM('Sales'[Rooms Sold] ),
    FILTER( ALL( Sales ), 
        Sales[Revenue Date] = MAX( Dates[Date] ) &&
        Sales[Reading Date] = cDate )) 

.
Repeated the same process for the second page. Although there is no single date selection via a Slicer, this is the basic pattern.

-3 Past v2 = 
CALCULATE( SUM('Sales'[Rooms Sold] ),
    FILTER( ALL( Sales ), 
        Sales[Revenue Date] = MAX( Dates[Date] ) &&
        Sales[Reading Date] = CALCULATE( MAX( Dates[Date] ), DATEADD( Dates[Date], -3, DAY ))))

Here is my updated sample file, I hope this is helpful.
eDNA - Revenue multiple Date tables v2.pbix (1.9 MB)

This is amazing! Thanks a million. I just made a tiny change to the formula in the event the reading date is in the past and does not equal to the current date. Its amazing that the formula works without an active relationship with the date table.

-3 days v2 =
VAR cDate = MAX(‘Sales_Revenue Fact sheet’[Reading Date])
RETURN

CALCULATE( SUM(‘Sales_Revenue Fact sheet’[Rooms Sold] ),
FILTER( ALL( ‘Sales_Revenue Fact sheet’),
‘Sales_Revenue Fact sheet’[Revenue Date] = MAX(‘Calendar’[Date]) &&
‘Sales_Revenue Fact sheet’[Reading Date] = cDate-3))

I have a question on the the following; what does the formula do in this context? I just want to understand the part of this calculation better.

FILTER( VALUES( Sales[Revenue Date] ), Sales[Revenue Date] IN VALUES( Dates[Date] )))

Once again, a big thank you.

VALUES( Sales[Revenue Date] )
returns a one-column table that contains the distinct values from the specified table or column. In other words, duplicate values are removed and only unique values are returned.

Sales[Revenue Date] IN VALUES( Dates[Date] )
from all those dates we only keep the ones that are visible in the current context of Dates[Date]

1 Like