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

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.