Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure to return value from column between dates

Hi, 

 

I have a table formated as : 

 

ID          StartDate               EndDate             FTE

A1          10/11/2016          30/09/2019          92
A1          01/10/2019                                      100

A2          01/01/2019          30/06/2019         100

A2          01/07/2019                                      60

 

I am trying to create a measure which will return  (it must be a measure or varible as it will be used to perfom a calculation in another measure) :

92 for ID A1 between NOV/2016 up to SEP/2019 and 100 from OUT/2019

100 for ID A2 between JAN/2019 up to JUN/2019 and 60 from JUL/2019

 

I have this table and a Calendar table, inactive interfaece. 

 

Any idea?  

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Just use SUM() instead of MAX() in the formula and you will get correct total value.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

     

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak , 

       

      Thanks for your reply, however this file doesnt help in this case.  As I do no want quantity, I would like to get the value back. 

       

      So having in mind this table: 

       

      ID          StartDate               EndDate             FTE

      A1          10/11/2016          30/09/2019          92
      A1          01/10/2019                                      100

      A2          01/01/2019          30/06/2019         100

      A2          01/07/2019                                      60

       

       

      So I create a measure : 

       

      Correct FTE= MAX(Table [FTE])

       

      But this measure is retuning always the biggest value and the opposite happens if i cahnge it to MIN. 

       

      what I am trying to achive is to return the value of the column FTE between the date range, with no calculation. 

       

      So having this tbale as axemple: 

      ID A1, for any date I pick between NOV/16 and SEP/19 the return must be 92 and From OCT/19 100. 

      ID A2, for any date I pick between JAN/19 and JUN/19 the return must be 100 and from JUL/19  60 . 

       

      This figure is pivot to be used in another measure.