Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi :
I want to create a new column or measure by dax, but i don't know how. The conditions like below.
Here i have a table with 3 column call "sales data".
Billing date | AOP | AOP$ | New column |
2021/1/1 | AOP | 123.00 |
|
2021/1/1 | Actual | 2,466.00 |
|
2021/2/2 | AOPJAN | 31,655.00 |
|
2021/6/6 | AOPJAN | 13,345.00 |
|
2021/6/6 | AOPJUL | 5,623.00 |
|
2022/3/3 | AOPJAN | 56,677.00 |
|
2022/8/8 | AOPJAN | 46,267.00 |
|
1.if AOP=Actual or AOP, [new column]=0
2.If Year-Quarter, there has AOPJUL, [new column] = AOP$,
and the same Year-Quarter which AOP=AOPJAN, [new column] = 0
3.If Year-Quarter dosen't exit AOP = AOPJUL, [new column] = AOP$ (which AOP = AOPJAN)
For example :
Billing date | (example) | AOP | AOP$ | New column |
2021/1/1 | 2021-Q1 | AOP | 123.00 | 0 |
2021/1/1 | 2021-Q1 | Actual | 2,466.00 | 0 |
2021/2/2 | 2021-Q1 | AOPJAN | 31,655.00 | 31,655.00 |
2021/7/7 | 2021-Q3 | AOPJAN | 13,345.00 | 0 |
2021/7/7 | 2021-Q3 | AOPJUL | 5,623.00 | 5,623.00 |
2022/3/3 | 2022-Q1 | AOPJAN | 56,677.00 | 56,677.00 |
2022/8/8 | 2022-Q3 | AOPJAN | 46,267.00 | 46,267.00 |
Thanks .
Solved! Go to Solution.
Hi @Anonymous
Thank you for the clarifications.
See here sample file https://www.dropbox.com/t/exwkvsK4LPyEWe2B
I hope this soves your problem
New Column =
VAR CurrentYearQuarter =
YEAR ( 'Sales Data'[Billing date] ) * 10 + QUARTER ( 'Sales Data'[Billing date] )
VAR Result =
SWITCH (
TRUE (),
'Sales Data'[AOP] IN { "AOP", "ACTUAL" }, 0,
CALCULATE (
DISTINCTCOUNT ( 'Sales Data'[AOP] ),
FILTER ( 'Sales Data', YEAR ( 'Sales Data'[Billing date] ) * 10 + QUARTER ( 'Sales Data'[Billing date] ) = CurrentYearQuarter
&& NOT ( 'Sales Data'[AOP] IN { "AOP", "ACTUAL" } ) )
)
> 1, IF ( 'Sales Data'[AOP] = "AOPJAN", 0, 'Sales Data'[AOP$] ),
'Sales Data'[AOP$]
)
RETURN
Result
@Anonymous
Are trying to create a calculted column (new column) or a measure (new measure)?
i am trying to create a new column but not yet successful.
can new column or new measure do what i want?
Hi Rebecca
both are possible but conditions you've provided are not so clear. Can you explain further?
Hi tamerj1
I wili have two AOP datas per year,
the first one (AOPJAN) shows the data for a whole year and the second (AOPJUL) shows the data for the second half of the year. So when my second data comes in, it has to replace the first data. for the second half of the year.(The original AOPJAN data in the second half of the year will be replaced by AOPJUL.)
Hi @Anonymous
Thank you for the clarifications.
See here sample file https://www.dropbox.com/t/exwkvsK4LPyEWe2B
I hope this soves your problem
New Column =
VAR CurrentYearQuarter =
YEAR ( 'Sales Data'[Billing date] ) * 10 + QUARTER ( 'Sales Data'[Billing date] )
VAR Result =
SWITCH (
TRUE (),
'Sales Data'[AOP] IN { "AOP", "ACTUAL" }, 0,
CALCULATE (
DISTINCTCOUNT ( 'Sales Data'[AOP] ),
FILTER ( 'Sales Data', YEAR ( 'Sales Data'[Billing date] ) * 10 + QUARTER ( 'Sales Data'[Billing date] ) = CurrentYearQuarter
&& NOT ( 'Sales Data'[AOP] IN { "AOP", "ACTUAL" } ) )
)
> 1, IF ( 'Sales Data'[AOP] = "AOPJAN", 0, 'Sales Data'[AOP$] ),
'Sales Data'[AOP$]
)
RETURN
Result
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |