Measure to return value from column between dates

[Anonymous]

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?  

[amitchandak]

@Anonymous , not very clear refer if this file can help

[Anonymous]

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. 

[v-jayw-msft]

Hi @Anonymous ,

Check this measure.

Measure = CALCULATE(MAX('Table'[FTE]),FILTER('Table','Table'[StartDate]<=SELECTEDVALUE('date'[Date])&&('Table'[EndDate]>=SELECTEDVALUE('date'[Date])||'Table'[EndDate]=BLANK())))

Result would be shown as below.

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.

[Anonymous]

@v-jayw-msft this is worked when you look at line results, however the total is not totalizing. 

So in your first example the total should be 192, second example 152 and third 160. 

do you know how to get this ? 

[v-jayw-msft · Accepted solution]

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.

[Anonymous]

thanks it worked perfectly. 

thanks so much. 

[amitchandak]

@Anonymous , refer this link. I will also try to check the solution on your data

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970