calculate new table

Hi Anonymous ,

Believe this is possible using a measure however to create a new table you can do the following.

On the query editor follow the steps below:

• Reference table A
• Add a custom column with the following code:

try {Number.From([date_in])..Number.From([date_out])} otherwise {Number.From([date_in])..Number.From(  Date.From( DateTime.LocalNow())  )}

• Expand the new column
• Format the column has date
• Add a new column with the following code:

try if (if [order_date] = null then [Custom] >= [date_in] else [Custom] >= [date_in] and 
[Custom] < [order_date])

 then 1 else
 
 if(if [date_out] = null then [Custom] >= [order_date] else [Custom] >= [order_date] and 
[Custom] < [date_out])

  
  
  then 0 else null

otherwise null

Now you have the new table: