Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jwi1
Post Patron
Post Patron

calculate new table

Hi all,

Hope someone can help me with the following.

 

I have a table A:

ctr_nrdate_inorder_datedate_out
A1-6-20224-6-20225-6-2022
B2-6-202216-6-2022 
C15-6-2022 23-6-2022
D20-6-202225-6-202226-6-2022
E22-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_nr1-6-20222-6-20223-6-20224-6-20225-6-20226-6-20227-6-20228-6-20229-6-202210-6-202211-6-202212-6-202213-6-202214-6-202215-6-202216-6-202217-6-202218-6-202219-6-202220-6-202221-6-202222-6-202223-6-202224-6-202225-6-202226-6-2022
A11100                     
B 1111111111111100000000000
C              111111110   
D                   1111100
E                     11111
Total12211111111111211112232211

 

Thanks upfront for your advise!

 

John

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

MFelix_0-1656409850320.png

 

MFelix_1-1656409865517.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
jwi1
Post Patron
Post Patron

Hi @MFelix 

 

Problem solved, thanks a lot!

 

John

MFelix
Super User
Super User

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:

MFelix_0-1656409850320.png

 

MFelix_1-1656409865517.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.