create a new column or measure with filter data from another column

[Anonymous]

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 .

[tamerj1]

@Anonymous 

Are trying to create a calculted column (new column) or a measure (new measure)?

[Anonymous]

i am trying to create a new column but not yet successful.

can new column or new measure do what i want?

[tamerj1]

Hi Rebecca

both are possible but conditions you've provided are not so clear. Can you explain further?

[Anonymous]

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.)

[tamerj1 · Accepted 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]

Hi @tamerj1 :

Thank you so much to solve my problem !!😁