Dynamic X axis range based on the current Month

[Steamedbunz]

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
)

Date	WK MonthStart	WK MonthEnd
27/02/2024	01/02/2024	27/02/2024
28/02/2024	01/02/2024	27/02/2024
29/02/2024	01/02/2024	27/02/2024
01/03/2024	28/02/2024	26/03/2024
02/03/2024	28/02/2024	26/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. 

[v-cgao-msft · Accepted solution]

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 

[rajendraongole1]

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]

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,