Measure that calculate the days between one entry date.

[Vitore]

Hello.

I have some problems to elaborate this measure.

In a table i have two dates, the initial date and the end date of object that stay in stock. Between this dates have the quantity of the days. Now i need to get all objects that enter until the day 15/01 , and calculate how many days(as interger) that object stays in stock until day 15, even if that object stays more time. Then i can calculate the sum of days for all objects that enter until the day 15 and stay until day 15.

I don't know how to do this with measures. Futhermore i need a slicer for dynamically change this date (in the case 15). 

Anyone could help me on this?

I would be very grateful.

att,

[v-yangliu-msft]

Hi  @Vitore ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select =SELECTEDVALUE('Table'[ Entry date])
var _max=CALCULATE(MAX('Table'[Leave Date]),FILTER(ALL('Table'),'Table'[ Entry date]=_select))
var _min=CALCULATE(MAX('Table'[Leave Date]),FILTER(ALL('Table'),'Table'[Leave Date]<_max))
return
DATEDIFF(_min,_max,DAY)

2. Result:

Does this answer match your expected result

Best Regards,

Liu Yang

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

[Vitore]

I re-explain.
I have two columns, the entry date of a product, and the leave date of a product. In another table i have a dimension table of days. 

I need to select a date in dimension table of days(as a filter), then this date filter the products that have entry date lower than the selected date, and calculate until the selected date how many days the product have betwen the entry date and selected date OR if lower than leave date, use the leave date to calculate. And i need the sum of days that the product stayed in stock.

Thnx for help.

[Fowmy]

@Vitore 

Can you provide an example with some sample data and the expected results?

 

[Vitore]

Entry date |   Waiting days | Leave Date
01/01                   5                  05/01

06/01                    2                 08/01

03/01                    10               13/01

04/01                   20                24/01 

These rows are related to a product. Now i need to get all product that entry until day 15 and calculate how many days, the product has as waiting days. Exemple, the product that enter 04/01 and stays 20 days, only count as 11 days.