Calculate Column - Check Row Record

[KH_Mike]

Hi All,

I have a data like below table. Now, I would like to create a new column as Slicer for easy comparsion. Is it possible to create under below condition? If not, any other way to acheive the have the same result?

Show "Y" only if "Acutal" and "Forecast" both contain the same "YearMonth" record

Actual / Forecast	YearMonth	New Column
Actual	202101	Y
Actual	202102	Y
Actual	202103	Y
Actual	202104	Y
Actual	202105	Y
Forecast	202101	Y
Forecast	202102	Y
Forecast	202103	Y
Forecast	202104	Y
Forecast	202105	Y
Forecast	202106	N
Forecast	202107	N

[v-angzheng-msft · Accepted solution]

Hi, @KH_Mike 

Try to create a calculated column like below:

_Col2 =
VAR _Int =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Global"
        ),
        [Year/Month]
    )
VAR _Glo =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Internal"
        ),
        [Year/Month]
    )
RETURN
    IF (
        'Table'[Actual/ Forecast] = "Foecast",
        "N",
        IF ( 'Table'[Year/Month] IN _Glo && 'Table'[Year/Month] IN _Int, "Y", "N" )
    )

Result:

Hope this helps

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft]

Hi, @KH_Mike 

Not very clear for me,  why rows 8 to 13 are N

[KH_Mike]

Hi @v-angzheng-msft 

Sorry for my unclear request. The logic should be like this

If [Actual / Forecast] = "Forecast" then "N"

If [Actual / Forecast] = "Actual" and both Internal & Global under [Internal / Global] contain same [YearMonth] then "Y" else "N"

[KH_Mike]

Hi All,

Anyone can help me? Thank you.

[Jihwan_Kim]

Hi, @KH_Mike 

Please check the below.

It is for creating a new column.

New Column CC =
VAR currentyearmonth = 'Table'[YearMonth]
VAR currentdescription = 'Table'[Actual / Forecast]
VAR conditions =
COUNTROWS (
FILTER (
'Table',
'Table'[Actual / Forecast] <> currentdescription
&& 'Table'[YearMonth] = currentyearmonth
)
)
RETURN
IF ( conditions = 1, "Y", "N" )

https://www.dropbox.com/s/45iqt5e2owswkee/khmike.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[KH_Mike]

Hi @Jihwan_Kim ,

I can't perform the same on my dateset. I guess the reason maybe my dateset have duplication on "YearMonth". For the same "YearMonth", I have serval rows of records contain different data (e.g.: Sales Name, Customer Name ...etc.). Do you have any ideas to handle this situation? Thank you.

[Jihwan_Kim]

Hi, @KH_Mike 

Thank you for your feedback.

Sorry that I quite do not understand your last question.

Please let me know how your expected result looks like.

Thanks.

[KH_Mike]

Hi @Jihwan_Kim 

What I want to show is like below. I try to use your DAX but can't show correctly.

The logic is like this.

If [Actual / Forecast] = "Forecast" then "N"

If [Actual / Forecast] = "Actual" and both Internal & Global under [Internal / Global] contain same [YearMonth] then "Y" else "N"