Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |