DAX - dynamic time range on trend chart issue

[kb83]

Hello,

Im struggling with a DAX measure that allow to switch time range dynamically as per selected time periods on the slicers.

I would like my chart to present only last 12 months time rage of recent data ('Date" column) as a default view but if somebody needs to select specific item in the Calendar Table ("Date") it should "reset" this 12 months default period and show the data in a selected time period like below conditional formula:
Customized Total = IF(ISFILTERED('Date'), SUM(Headcount[Employees]), CALCULATE(SUM(Headcount[Employees]), FILTER('Date', DATESINPERIOD('Date', MAX('Date'[Date]), -12, MONTH)))

I have to admit I did something similar already and it works perfectly however it was created on latest date not the whole range: example
Current No. Employees =
VAR CurrentEmployment = CALCULATE(MAX('Date'[Date]), ALL('Date'))
RETURN
CALCULATE(
SUM(Headcount[Employees]),
'Date'[Date] = CurrentEmployment
)
Any Hits, suggestions would be much appreciated.
Thank You

Ps. I tried Chat GPT already but with no success...

[123abc]

I 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:

Time Intelligence In DAX: How To Dynamically Select Starting Period | Master Data Skills + AI (enter...

www.sqlbi.com

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]

Hi, 
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.

[kb83]

Thank you for your reply. unfortunately, it is still not working properly. I created a test model and replicated conditions and used a measure formula you shared. 
When no 'Date' filter is applied it shows last 12 months and this is OK.

but when I want select a date which is ie before last 12 month's period it does not show any selected data...

Here is my data model looks like

I uploaded my pbix model for the review under following link
https://drive.google.com/file/d/1d1hnE4FxwguwN6YkPdsFSU6H_gBVBcxq/view?usp=drive_link

Regards

KB