Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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
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
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
Proud to be a Super User! | |
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,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
139 | |
113 | |
104 | |
64 | |
60 |