Help needed to DAX logic

Thank you for providing more details. To achieve the desired logic using one calculated column in Power BI DAX, you can follow these steps:

1. Create a Calculated Column in your Calendar table:

VisibleMonth =
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month])
VAR TodayDate = TODAY()
VAR ShowAllMonths =
IF(
CurrentYear < YEAR(TodayDate) || (CurrentYear = YEAR(TodayDate) && CurrentMonth <= MONTH(TodayDate)),
1,
0
)
VAR ShowPreviousMonth =
IF(
DAY(TodayDate) <= 21 &&
((CurrentYear = YEAR(TodayDate) && CurrentMonth = MONTH(TodayDate)) || (CurrentYear < YEAR(TodayDate))),
1,
0
)
RETURN
IF(
ShowAllMonths = 1 || ShowPreviousMonth = 1,
1,
0
)

1. Explanation:

• CurrentYear: Retrieves the selected year from the slicer.
• CurrentMonth: Retrieves the selected month from the slicer.
• TodayDate: Retrieves the current date.
• ShowAllMonths: Checks if the selected year is less than or equal to the current year and if the selected month is less than or equal to the current month. If so, it sets to 1.
• ShowPreviousMonth: Checks if the current date is less than or equal to the 21st day of the current month and if the selected year and month are equal to or less than the current year and month. If so, it sets to 1.
• VisibleMonth: Combines the conditions to determine if the month should be visible in the slicer. If either ShowAllMonths or ShowPreviousMonth is 1, it sets to 1, otherwise 0.

1. Usage:

• Use the VisibleMonth calculated column as a filter in your slicer for the month.
• Use the year slicer as it is.

This calculated column should dynamically adjust the visibility of months in your slicer based on the selected year and the current date, meeting your specified requirements. Adjust the DAX expressions as necessary to fit your data model and requirements.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Hi sthokchom,

Current power bi does not support to create dynamic calculated column/table based on filter effects. They weren't hosted on the same level and you can't use the child level to effect the parent level.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

To achieve the desired behavior with one calculated column in Power BI, you can use the following DAX expression. This expression will dynamically filter the months based on the selected year and the current date:

ShowMonth =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month Number])
VAR CurrentDay = DAY(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR ShowMonth =
IF(
SelectedYear < CurrentYear || (SelectedYear = CurrentYear && CurrentDay >= 21),
1,
IF(
SelectedYear = CurrentYear && CurrentDay < 21 && CurrentMonth <= MONTH(TODAY()),
1,
0
)
)
RETURN
ShowMonth

Explanation of the logic:

• The SelectedYear variable stores the value of the selected year from Slicer 1.
• The CurrentMonth variable stores the value of the selected month from Slicer 2.
• The CurrentDay variable stores the current day of the month.
• The CurrentYear variable stores the current year.
• The ShowMonth variable checks the conditions for showing the month:
  • If the selected year is less than the current year, it returns 1, indicating that the month should be shown.
  • If the selected year is the current year and the current day is 21 or greater, it returns 1, indicating that the month should be shown.
  • If the selected year is the current year, the current day is less than 21, and the current month is less than or equal to the selected month, it returns 1.
  • Otherwise, it returns 0.

This calculated column will dynamically control the visibility of months in Slicer 2 based on the selected year and the current date, implementing the logic you described.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Thank you for providing additional clarification. To implement the logic you described with one calculated column, you can create a calculated column in your calendar table that evaluates whether each month should be visible based on the selected year and the current date. Here's the DAX logic:

VisibleMonth =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month])
VAR CurrentDate = TODAY()
VAR FirstDayOfMonth = DATE(SelectedYear, SelectedMonth, 1)
VAR ShowMonth =
IF(
SelectedYear < YEAR(CurrentDate),
1,
IF(
SelectedYear = YEAR(CurrentDate) && SelectedMonth < MONTH(CurrentDate),
1,
IF(
SelectedYear = YEAR(CurrentDate) && SelectedMonth = MONTH(CurrentDate) && DAY(CurrentDate) >= 21,
1,
0
)
)
)
RETURN
ShowMonth

This calculated column VisibleMonth works as follows:

• It first checks if the selected year is less than the current year. If so, it returns 1 (visible).
• If the selected year is the same as the current year, it checks if the selected month is less than the current month. If so, it returns 1.
• If the selected year and month are the same as the current year and month, it checks if the current day is greater than or equal to 21. If so, it returns 1.
• Otherwise, it returns 0 (not visible).

Once you have this calculated column in your calendar table, you can use it as a filter in your report to control the visibility of months based on your specified conditions.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.