Help needed to DAX logic

[sthokchom]

Hi Community,

Could you please help me solving the below use case.

I have calender table consists of year and month. I have both year and month values as a 2 different slicer on report. Now I want to control months based on the year for below 2 use case.
1. If I select 2024 and the current month is Feb, I should not see all the future months only Jan and Feb. However, if I select any previous year like 2023/2022, I shall see all of the months
2. I dont want to see prevous month, until 21st of each month. For example, in the month of march, from 1st march to 20th march, I only want to see Jan and from 21st onward, I should see Feb month. Same goes for other months as well

[sthokchom]

Hi @123abc ,

Thanks for helping me out. However, I am looking for something that will be solved this using one calcualted column.

Below is detialed use case:

Slicer 1 : Year (Values:2024, 2023, 2022 etc)

Slicer 2 : Month (Values : Jan - Dec)

Now, what I am trying to acheive is when I select 2024 in the Slicer 1, I should only see months in Slicer 2 that have been passed incluing the logic that I don't want to see previous month, until 21st of each month. Since we are in Jan , so nothing should come up for 2024. Once Feb 21st 2024 arrives, I need to see Jan value in Slicer 2 and it will continue for the rest of months. When I select any previous year in Slicer 1, I should all the of the months.

Hope this makes sense.

[123abc]

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.

[123abc]

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.

[v-shex-msft]

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

[sthokchom]

Hi @123abc ,

Thanks for your valuavle input. But none of those are working. When I select 2024 in Slicer 1, nothing happnes in slicer 2. All the months still get displayed.

[123abc]

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.

[123abc]

To achieve the described logic in DAX (Data Analysis Expressions) for Power BI or any other tool using DAX, you can use the following steps:

1. Create a calculated column for the display month:

DisplayMonth =
VAR CurrentDate = TODAY()
RETURN
IF(
'Calendar'[Year] = YEAR(CurrentDate) && 'Calendar'[MonthNumber] > MONTH(CurrentDate),
BLANK(),
'Calendar'[Month]
)

This calculated column checks if the year is the current year and the month is greater than the current month. If true, it returns a blank; otherwise, it returns the month.

1. Create a calculated column for the display month based on the 21st cutoff:

DisplayMonth21stCutoff =
VAR CurrentDate = TODAY()
VAR CutoffDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 21)
RETURN
IF(
'Calendar'[Date] <= CutoffDate,
BLANK(),
'Calendar'[Month]
)

This calculated column checks if the date is less than or equal to the 21st of the current month. If true, it returns a blank; otherwise, it returns the month.

After adding these calculated columns to your calendar table, you can use them in your reports and slicers.

For the first use case, use the DisplayMonth column in your slicer for the month.

For the second use case, use the DisplayMonth21stCutoff column in your slicer for the month.

Remember to adjust column and table names according to your actual data model. These calculated columns assume that you have a 'Calendar' table with 'Year', 'MonthNumber', 'Month', and 'Date' columns.

Feel free to modify these calculations based on your specific requirements and data model.

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.