Forum Discussion
Helper IDAX - dynamic time range on trend chart issue
Community ChampionI understand that you want to create a dynamic time range on your trend chart based on the slicers. One possible solution is to use a separate calendar table for the slicer and create an inactive relationship with the main calendar table. Then you can use a measure to filter the last 12 months based on the selected date. You can find a detailed explanation and an example of this approach in this post.
Alternatively, you can use a measure to calculate the reference date based on the slicer selection and then use the DATESINPERIOD function to filter the last 12 months. For example, you can try something like this:
Reference Date =
VAR SelectedDate = MAX ( 'Calendar'[Date] )
VAR SelectedYear = YEAR ( SelectedDate )
VAR SelectedMonth = MONTH ( SelectedDate )
VAR SelectedQuarter = QUARTER ( SelectedDate )
VAR SelectedYearMonth = SelectedYear * 100 + SelectedMonth
VAR SelectedYearQuarter = SelectedYear * 10 + SelectedQuarter
RETURN
SWITCH (
TRUE (),
ISFILTERED ( 'Calendar'[Year] ) && ISFILTERED ( 'Calendar'[Quarter] ), EOMONTH ( DATE ( SelectedYearQuarter / 10, SelectedYearQuarter - SelectedYear * 10 * 3, 1 ), 0 ),
ISFILTERED ( 'Calendar'[Year] ) && ISFILTERED ( 'Calendar'[Month] ), EOMONTH ( DATE ( SelectedYearMonth / 100, SelectedYearMonth - SelectedYear * 100, 1 ), 0 ),
ISFILTERED ( 'Calendar'[Date] ), SelectedDate,
MAX ( 'Calendar'[Date] )
)
Last 12 Months Filter =
VAR ReferenceDate = [Reference Date]
VAR PreviousDates = DATESINPERIOD ( 'Calendar'[Date], ReferenceDate, -12, MONTH )
RETURN
IF ( 'Calendar'[Date] IN PreviousDates, 1, 0 )Then you can use the Last 12 Months Filter measure as a visual level filter and set it to 1.
I hope this helps you solve your issue. If you have any further questions, please let me know. 😊
Plz follow these links may solve your issue:
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.
Helper IHi,
Thank you for your reply. Actually I want my trend chart shows last 12 months as default chart view but if any Date filter is selected then it should "reset" this 12 months period from the chart view and behave like a standard view that reacts on the slicer selection i.e if user selects "January" it should show January only and not January with previous 12 months...Hope this explanaition is clear now.
- 123abc
Thank you for the clarification. If you want the default view to always show the last 12 months of data, regardless of any slicer selections, you can adjust the measure accordingly. Here's how you can modify the measure to achieve this behavior:
Customized Total =
CALCULATE(
SUM(Headcount[Employees]),
FILTER(
ALL('Date'),
'Date'[Date] > TODAY() - 365
&& 'Date'[Date] <= TODAY()
)
)This measure will always calculate the sum of employees for the last 12 months, regardless of any slicer selections. It ignores any filters applied to the 'Date' column, ensuring that the default view remains consistent.
When a user selects a specific date range using the slicer, it overrides the default view and shows data only for the selected period because the default calculation is replaced by the slicer selection.
This should achieve the behavior you described. Let me know if you have any further questions or if there's anything else I can assist you with!
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.
- kb83
Not exactly. I do want my measure changes as per slicer selection. So, again if have not any 'Date' slicers selected show me last 12 months. If I apply slicer show me the exact Date selection. So. i.e if select Year 2022 and 2023 I want the measue show whole date range from Jan 2022 till Dec 2023. Hope that clarifies it better
- 123abc
Thank you for the clarification. If you want your measure to dynamically adjust based on the slicer selection, showing the last 12 months by default but displaying the exact selected date range when slicers are applied, you can modify the DAX measure as follows:
Customized Total =
IF(
ISFILTERED('Date'[Date]),
CALCULATE(
SUM(Headcount[Employees]),
ALL('Date'),
'Date'[Date] >= MIN('Date'[Date]) && 'Date'[Date] <= MAX('Date'[Date])
),
CALCULATE(
SUM(Headcount[Employees]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Date] > DATE(YEAR(MAX('Date'[Date])) - 1, MONTH(MAX('Date'[Date])), DAY(MAX('Date'[Date])))
)
)
)In this modified measure:
- If a date filter is applied (ISFILTERED('Date'[Date])), it calculates the sum of employees based on the selected date range using MIN and MAX functions.
- If no date filter is applied, it calculates the sum of employees for the last 12 months, similar to before.
This measure should now display the default last 12 months of data when no slicers are applied, and it will adjust dynamically to show the exact selected date range when slicers are applied to the 'Date' column.
If I answered your question, please mark my post as solution, Appreciate your Kudos.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.