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 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

7 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    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.

  • 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
      Community 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

        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's avatar
      123abc
      Community 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.