Forum Discussion

budimir's avatar
budimir
Regular Visitor
5 years ago
Solved

Calculated column where start date is fixed?

I have a calculated column where I get qty for a specific warehouse, but the problem is that the start date should always be fixed so the user can't change it (01/01/2009) and the end date is changing depending on the user selection. How can I fix the start date of that column so it doesn't change when the user sets a different start and end date?

 

Please, let me know if you need more info.

 

This is how my column is calculated:

Veleprodajno Skladište = IF(Zaliha[Šifra lokacije] = "D1V",[Zaliha (kom)],0)
 
Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, budimir 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    There is no relationship between two tables. You may create a measure as below.

    Visual Control = 
IF(
    SELECTEDVALUE('Table'[Date])>=DATE(2009,1,1)&&
    SELECTEDVALUE('Table'[Date])<=MIN('Calendar'[Date]),
    1,0
)

     

    Then you need to put the measure in the visual level filter and use 'Date' column from 'Calendar' to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    5 years ago

    Hi budimir ,

     

    Can you share more around your data please?

    Do you want a static date check added to the caluclation that you have shared? I am not able to understand if you want a new calculation or want to add this date check to the existing one?

     

     

    Thanks,

    Pragati

     

    • budimir's avatar
      budimir
      Regular Visitor
      5 years ago

      Hi Pragati11, 

       

      Let me clarify a bit. I have a table with Rolling Calendar Lookup and it has a relation to a table where I have stock numbers. To have that stock accurately displayed to a user it has to start from the first date in that stock table which is 01/01/2009.

      On a visual, I have a date selection from that Rolling Calendar Lookup and the problem occurs when a user sets a different start date. Numbers for stock are all messed up at that point.

      I would need a new calculation that would display a new number based on user selection but the start date is always the same. Few examples below:

      If a user sets 01/01/2020 to 01/02/2020 - I need to show 01/01/2009 to 01/02/2020

      If a user sets 01/05/2018 to 01/05/2020 - I need to show 01/01/2009 to 01/05/2020

      If a user sets 01/03/2016 to 05/05/2016 - I need to show 01/01/2009 to 05/05/2016

       

      I hope I managed to clarify my problem.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, budimir 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    There is no relationship between two tables. You may create a measure as below.

    Visual Control = 
IF(
    SELECTEDVALUE('Table'[Date])>=DATE(2009,1,1)&&
    SELECTEDVALUE('Table'[Date])<=MIN('Calendar'[Date]),
    1,0
)

     

    Then you need to put the measure in the visual level filter and use 'Date' column from 'Calendar' to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.