Forum Discussion
Helper IDAX - dynamic time range on trend chart issue
Community ChampionThank 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.
Helper INot 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
RegardsKB