Forum Discussion
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
)
| 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.
- Anonymous1 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 __filterBest Regards,
Gao
Community Support TeamIf 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
- rajendraongole1Super User
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
- SteamedbunzFrequent 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,
- AnonymousNot 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 __filterBest Regards,
Gao
Community Support TeamIf 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