cancel
Showing results for
Did you mean:

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.

Helper III

Calculate Column - Check Row Record

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
1 ACCEPTED SOLUTION
Community Support

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.

8 REPLIES 8
Community Support

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.

Community Support

Hi, @KH_Mike

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

Helper III

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"

Helper III

Hi All,

Anyone can help me? Thank you.

Super User

Hi, @KH_Mike

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

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.

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

Helper III

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.

Super User

Hi, @KH_Mike

Sorry that I quite do not understand your last question.

Thanks.

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

Helper III

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"

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors