Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
try {Number.From([date_in])..Number.From([date_out])} otherwise {Number.From([date_in])..Number.From( Date.From( DateTime.LocalNow()) )}
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
try {Number.From([date_in])..Number.From([date_out])} otherwise {Number.From([date_in])..Number.From( Date.From( DateTime.LocalNow()) )}
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |