Forum Discussion

jsierkstra's avatar
jsierkstra
Frequent Visitor
3 years ago
Solved

How to model date relationship on multiple date columns

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 ha...
Need Help
  • TonyZhou1980's avatar
    TonyZhou1980
    3 years ago

    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's avatar
jsierkstra
Frequent Visitor
3 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago
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's avatar
    jsierkstra
    Frequent Visitor
    3 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      It dramatically reduces the size of the auto date tables.