Need formula to apply in power bi report

Question

I need to add one calculated column in power bi dax. In my excel data there is month end column as shown in table.

As you can see, W05 is last week of 01/2020 (Report Month), so month end is Y, for rest it is N. Same way, W09 is last week of 02/2020 (Report month), month end= Y. rest is N. Can you help me with the formula I can use in power bi to poplulate end month (N or Y)?

Answer 1

Create this following measure-

is_last_week = 

var current_report_week = MIN(your_table_name[report week])
var current_minth_max_report_week =
CALCULATE(
    MAX(your_table_name[report week]),
    ALLEXCEPT(your_table_name,your_table_name[report month])
)

RETURN 
IF(
    current_minth_max_report_week = current_report_week,
    "Y",
    "N"
)

For Custom Column, use this below code-

is_last_week = 

var current_report_week = your_table_name[report week]
var current_month_max_report_week =
CALCULATE(
    MAX(your_table_name[report week]),
    ALLEXCEPT(your_table_name,your_table_name[report month])
)

RETURN 
IF(
    current_month_max_report_week = current_report_week,
    "Y",
    "N"
)

Here is the final output-