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.
Hello All,
Consider the following dataset
Row | Week | Field1 | Field2 | Value1 | Value2 | Week Age |
1 | 202001 | A | A | 10 | 20 | 0 |
2 | 202002 | A | A | 10 | 20 | 1 |
3 | 202003 | A | A | 10 | 20 | 2 |
4 | 202004 | A | A | 10 | 10 | |
5 | 202001 | B | N/A | 30 | 0 | |
6 | 202002 | B | N/A | 30 | 1 | |
7 | 202001 | N/A | C | 40 | 0 | |
8 | 202002 | N/A | C | 40 | 1 |
I need to calculate the 'Week Age' column in PowerBI Desktop.
Scenario 1
For Rows 1, 2 and 3 there is a mismatch between Value 1 and value 2 for the two fields for the 3 weeks, hence the 'Week Age' should increment by 1 until there is a mismatch.
For row 4, there should be no 'Week Age' as the mismatch doesn't exist any more.
Scenario 2
For rows 5,6,7,8 there are cases where one of the fields has a missing value or 'N/A'. I would need to calculate 'Week Age' in this case as well.
Please let me know in case of any clarifications.
Solved! Go to Solution.
Hi @apollo89 ,
You can create 2 calculated columns as below to achieve it:
1. Judge the value of Value1 and Value2 is equal or not: if equal, blank; otherwise, 1
Equal or not equal = IF('Week Age'[Value1]='Week Age'[Value2],BLANK(),1)
2. Create a calculated column to get week age based on the judge column
Week Age =
IF (
ISBLANK ( [Equal or not equal] ),
BLANK (),
CALCULATE (
COUNT ( 'Week Age'[Equal or not equal] ),
FILTER (
'Week Age',
'Week Age'[Equal or not equal] = 1
&& 'Week Age'[Row] <= EARLIER ( 'Week Age'[Row] )
&& 'Week Age'[Field1] = EARLIER ( 'Week Age'[Field1] )
)
) - 1
)
Best Regards
Rena
Hi @apollo89 ,
You can create 2 calculated columns as below to achieve it:
1. Judge the value of Value1 and Value2 is equal or not: if equal, blank; otherwise, 1
Equal or not equal = IF('Week Age'[Value1]='Week Age'[Value2],BLANK(),1)
2. Create a calculated column to get week age based on the judge column
Week Age =
IF (
ISBLANK ( [Equal or not equal] ),
BLANK (),
CALCULATE (
COUNT ( 'Week Age'[Equal or not equal] ),
FILTER (
'Week Age',
'Week Age'[Equal or not equal] = 1
&& 'Week Age'[Row] <= EARLIER ( 'Week Age'[Row] )
&& 'Week Age'[Field1] = EARLIER ( 'Week Age'[Field1] )
)
) - 1
)
Best Regards
Rena
Covering 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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |