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.
     
TonyZhou1980's avatar
TonyZhou1980
Resolver I
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

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

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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