Forum Discussion

Steamedbunz's avatar
Steamedbunz
Frequent Visitor
1 year ago
Solved

Dynamic X axis range based on the current Month

Hi, 

 

I have this Area Chart that tracks Inbox/Outbox email quantities overview. On the Y axis it will be the number of emails. On the X axis it will be days listed for current month. 

 

However, our company has very specific ways to calculate company financial months. So the days with never match any calendar Month days. 

 

I've setup an calendar table, where it has all the normal dates in a calumn and I used DAX code to calculate company specific Start and End of that Month. 

For example:

WK MonthEnd =
VAR InputDate = [Date] -- Normal Calendar Input
VAR MonthNum = MONTH(InputDate) -- Get the month number
VAR LastDayOfMonth = EOMONTH(InputDate, 0) -- Get the last day of the month
VAR DayOfWeek = WEEKDAY(LastDayOfMonth, 2) -- Get the weekday (1 = Monday, 7 = Sunday)

-- Check if the input date falls in January or July
RETURN
IF(
MonthNum = 1 || MonthNum = 7, -- If the month is January or July
DATE(YEAR(InputDate), MonthNum, 31), -- Return 31st of January or July
LastDayOfMonth - MOD(DayOfWeek + 5, 7) -- Otherwise, calculate the last Tuesday
)

DateWK MonthStartWK MonthEnd
27/02/202401/02/202427/02/2024
28/02/202401/02/202427/02/2024
29/02/202401/02/202427/02/2024
01/03/202428/02/202426/03/2024
02/03/202428/02/202426/03/2024

 

How do I set the X axis range to display min/max of the calculated MonthStart and MonthEnd? Assume I have to write a measure as filter that checks for the current calendar month we're in, and look up the boundaries from different column? Or is there an easy way to archieve this?

 

Basically I want the X axis range to be dynamic that changes from Month to Month. 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Steamedbunz ,

     

    Try applying the following measure to a visual filter:

    MEASURE = 
    VAR __today =
        TODAY ()
    VAR __cur_date =
        SELECTEDVALUE ( 'Table'[Date] )
    VAR __wk_monthstart =
        CALCULATE ( MAX ( 'Table'[WK MonthStart] ), 'Table'[Date] = __today )
    VAR __wk_monthend =
        CALCULATE ( MAX ( 'Table'[WK MonthEnd] ), 'Table'[Date] = __today )
    VAR __filter =
        IF ( __cur_date >= __wk_monthstart && __cur_date <= __wk_monthend, 1 )
    RETURN
        __filter

    Best Regards,
    Gao

    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum 

3 Replies

  • Hi Steamedbunz - Create two measures to calculate the WK MonthStart and WK MonthEnd for the current month, as the min and max range for the X-axis.

    Min_X_Axis_Date =
    CALCULATE(
    MIN('Calendar'[WK MonthStart]),
    FILTER(
    'Calendar',
    'Calendar'[Date] >= TODAY() && 'Calendar'[Date] <= EOMONTH(TODAY(), 0)
    )
    )

     

    another measure for max date

    Max_X_Axis_Date =
    CALCULATE(
    MAX('Calendar'[WK MonthEnd]),
    FILTER(
    'Calendar',
    'Calendar'[Date] >= TODAY() && 'Calendar'[Date] <= EOMONTH(TODAY(), 0)
    )
    )

    Use the Min_X_Axis_Date and Max_X_Axis_Date measures to filter your visual.
    Apply these filters in the Filters pane to limit the X-axis range. For example, you can create a slicer or set up the visual-level filter to show only the dates between these two calculated values.

     

    hope this helps you

    • Steamedbunz's avatar
      Steamedbunz
      Frequent Visitor

      Hi rajendraongole1 ,

       

      Thanks for your reply. I've created those measures sucessfully and has verified the dates were indeed correct. 

       

      However, how can I apply those to filter a visual? When put in those filters, I've tried few different options, it still asked me to manually select a date? 

       

       

       

      Regards, 

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Steamedbunz ,

     

    Try applying the following measure to a visual filter:

    MEASURE = 
    VAR __today =
        TODAY ()
    VAR __cur_date =
        SELECTEDVALUE ( 'Table'[Date] )
    VAR __wk_monthstart =
        CALCULATE ( MAX ( 'Table'[WK MonthStart] ), 'Table'[Date] = __today )
    VAR __wk_monthend =
        CALCULATE ( MAX ( 'Table'[WK MonthEnd] ), 'Table'[Date] = __today )
    VAR __filter =
        IF ( __cur_date >= __wk_monthstart && __cur_date <= __wk_monthend, 1 )
    RETURN
        __filter

    Best Regards,
    Gao

    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum