calculate new table

[jwi1]

Hi all,

Hope someone can help me with the following.

I have a table A:

ctr_nr	date_in	order_date	date_out
A	1-6-2022	4-6-2022	5-6-2022
B	2-6-2022	16-6-2022
C	15-6-2022		23-6-2022
D	20-6-2022	25-6-2022	26-6-2022
E	22-6-2022

I want to calculate a new table B, based on table A.

The logic is as follows:

From date_in, the value 1 is mentioned in table B;

When the order date is filled: from that date the value 0 is mentioned in table B instead of value 1;

From the date_out, the value 0 stops in table B;

After that we can calculate the sum for every date column

Table B:

ctr_nr	1-6-2022	2-6-2022	3-6-2022	4-6-2022	5-6-2022	6-6-2022	7-6-2022	8-6-2022	9-6-2022	10-6-2022	11-6-2022	12-6-2022	13-6-2022	14-6-2022	15-6-2022	16-6-2022	17-6-2022	18-6-2022	19-6-2022	20-6-2022	21-6-2022	22-6-2022	23-6-2022	24-6-2022	25-6-2022	26-6-2022
A	1	1	1	0	0
B		1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0
C															1	1	1	1	1	1	1	1	0
D																				1	1	1	1	1	0	0
E																						1	1	1	1	1
Total	1	2	2	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	2	2	3	2	2	1	1

Thanks upfront for your advise!

John

[jwi1]

Hi @MFelix 

Problem solved, thanks a lot!

John

[MFelix · Accepted solution]

Hi @jwi1 ,

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: