How to model date relationship on multiple date columns

[jsierkstra]

Hi,

I have a problem which seems easy to solve but I am not able to. Let's explain. I will simplify this in order to make it more clear.

I have a table, let's callet it Pallets. This table has three columns. Whenever Date Out has been set to 01-01-1753, it means that the pallet is still in stock.

Pallet Code	Date In	Date Out
123	14-11-2022	20-11-2022
456	14-11-2022	01-01-1753
789	21-11-2022	21-11-2022

I have a date table with a fixed amount of dates.

Date

14-11-2022

...

21-11-2022

I want to present a slicer to the user in which they can select an individual date.

In this simplified example, when the user selects the date 14-11-2022, I want the following rows to be visible.

• 123 -> Yes
  • It was in stock on 14-11-2022 and out of stock on 20-11-2022 so that falls in the range for 14-11-2022.
• 456 -> Yes
  • It was in stock on 14-11-2022 and not yet out of stock. So that falls in the range.
• 789 -> No
  • It was not in stock on 14-11-2022 so that does not fall in the range.

When they select 21-11-2022, I want the following rows to be visible.

• 123 -> No
  • It was out of stock on 20-11-2022 which is before the selected date.
• 456 -> Yes
  • It was in stock on 14-11-2022 and not yet out of stock on the selected date.
• 789 -> Yes
  • It was in and out of stock on the same day, but it was the selected date.

-----------------------------------------------------------------------------

This can be easily solved in SQL with the following (psuedo-)query. 

SELECT *

FROM Pallets

WHERE [Date In] <= 'Selected date'

AND ([Date Out] => 'Selected date'  OR [Date Out] = '01-01-1753')

I tried the following things with mixed results:

• Create a table with the Dax-function: CALCULATETABLE(). I pass a filter to this function, but whenever I update my selected date, the calculated table won't update. 
• I created a directquery table and added parameters. But I was not succesfull in getting it to work whenever my date changes. 
• Created two relations from my date table to my Pallets table, one from date table to [Date In] and one from date table to [Date Out]. But this does not work because I can't specify the filter behaviour of the relationship. Besides, this creates one active relationship and one inactive. 

Is there a solution to this problem? 

---------------------------------------------------------

I cannot upload .pbix files directly to this forum, so I added a power bi file to my OneDrive.

https://1drv.ms/u/s!AoS_6AkTEXJzgYAPiWoBhj42BOneLQ?e=j5RNhX

[TonyZhou1980 · Accepted solution]

I tried to create a measure as below, and in the visual, set the "Still in Stock" in slice pane to equal to "yes", add a data[data] slice,

Still in Stock =

var _datein = calculate(max('palletcode'[Date In]),allexcept(palletcode,palletcode[Date In]))

var _dateout = CALCULATE(max('palletcode'[Date Out]),allexcept(palletcode,'palletcode'[Date Out]))

Var result =

    if(

        selectedvalue('Date'[Date])>=_datein && or(selectedvalue('Date'[Date])<=_dateout,_dateout=date(1753,1,1)),

        "Yes",

        "No"

    )

return

        if(ISINSCOPE(palletcode[Date In]),result,BLANK())

If you know how to attach the sample file, please tell me then I can attach the file for your reference.

[jsierkstra]

Hey, thanks for the effort. Unfortunately your proposed solution does not work. Whenever I select the date 14-11-2022, all the correct records show up. However, when I select 15-11-2022, none show up. This is incorrect behaviour because everything that was in stock on the 14th should also be in stock on the 15th. Because the date out are past 15-11-2022. 

Here is the attached file:

https://1drv.ms/u/s!AoS_6AkTEXJzgYARGfj95uSsY0iuYQ?e=XpulQB

[jsierkstra]

@TonyZhou1980 I misunderstood your solution. After thinkering with it, it finally clicked. It is working now! For completeness I will upload the working file. One of the problems that was interfering the correct working is that I still had relationships on the date table <-> pallets. This caused the returned records to be incorrect. Once I removed those, it works.

This is the working file.

https://1drv.ms/u/s!AoS_6AkTEXJzgYASSogDptsVzne2qw?e=DBValY

[v-cgao-msft]

Hi @jsierkstra ,

Please remove the relationship between these two tables.

In the formula, they pass a value here for filtering. This is fine, no additional filters are needed.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[lbendlin]

Read about inactive relationships and how to activate them with USERELATIONSHIP().

[jsierkstra]

Hi, thanks for your suggestion. Unfortunately I am not able to get it to work with USERELATIONSHIP(). 

I tried this

Pallets in stock = CALCULATETABLE('Pallets', FILTER('Pallets', [Date In] < Datetable[Date]), USERELATIONSHIP('Pallets'[Date In], Datetable[Date]))

But this give me the error that I need to use an aggregate function for the datetable. Whenever I use something like this:

Pallets in stock = CALCULATETABLE('Pallets', FILTER('Pallets', [Date In] < SELECTEDVALUE(Datetable[Date])), USERELATIONSHIP('Pallets'[Date In], Datetable[Date]))

It does not give me any result. If I use MAX(Datetable[Date]) or MIN(Datetable[Date])), it does not apply the correct selected date. It just picks the minimum/maximum value from the date table and applies that. 

[lbendlin]

Whenever Date Out has been set to 01-01-1753, it means that the pallet is still in stock.

Please reconsider this approach. It is much better to leave the value blank instead of specifying a very bad date.

[jsierkstra]

I agree with you, but leaving the value blank does not change the logic. It just replaces the check from 1-1-1753 to blank.

[lbendlin]

It dramatically reduces the size of the auto date tables.