Forum Discussion

sthokchom's avatar
sthokchom
Frequent Visitor
2 years ago

Help needed to DAX logic

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 ...
sthokchom's avatar
sthokchom
Frequent Visitor
2 years ago

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's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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.