Forum Discussion
Helper IDynamic date range slicer with only the max date (end date) selected and ignoring the min date
I have multiple filters that work well for Sales data, but when I need to display Inventory information, I want the filters to work slightly differently.
The filters in place are:
- Time Shift Filter (Previous Day, WTD, YTD, Full Year)
- Year (Data is sliced based on the selected year)
- Month
- Week
- Weekday
In my matrix, I have both Sales and Inventory data. For example, if today’s date is 10/12/2024:
When I select 2024 as the year and YTD (Year to Date) for the time shift filter, it automatically calculates the start date as 01/01/2024 and the end date as 10/12/2024.
However, for Inventory, I want the start date to always be fixed as 01/01/2022, regardless of the year selected. The end date should still be based on the selected filter. So, when I select YTD for 2024, the sum should be from 01/01/2022 to 10/12/2024, instead of starting from 01/01/2024.
Any help is appreciated!
Greg_Deckler Fowmy danextian bhanu_gautam Ritaf1983 rajendraongole1 amitchandak Ashish_Mathur
This worked for me:
ABC =
VAR enddate = CALCULATE(
MAX('Table'[Date]),
FILTER('Table', 'Table'[Year] = SELECTEDVALUE('Max Year'[Year]))
)
RETURN
CALCULATE(
SUM('Table'[Cost]),
'Table'[Date] <= enddate,
REMOVEFILTERS('Time Shift Table'), -- Remove all the tables which affect the date
REMOVEFILTERS('Table') -- In my case I had a Time shift table and a Date Table
)
Thanks!
6 Replies
Vijay_ChethanResolver III
Hello Jidnyasa2904, I do have solution but might not be the most optimal one
you could create a Measure to calculate the sum for inventory data rather than columns directly because if measures used, u can mention the start date in the filter in measure and also using ALL or ALLSELECTED based on whether you ant external filters to be applied, I'm unaware of your data structure so i dont think i can give you exact dax measure
hope this helps you, if yes please mark as solution.- Jidnyasa2904
Table Columns:
Date Category Location Balance
1/1/2022 A 1 10000
1/2/2022 A 1 10
1/3/2022 A 1
1/4/2022 A 1 -4
I have multiple filters, as mentioned above, so when I choose the year 2024 and the time shift YTD, I want the start date to be 1/1/2022 instead of 1/1/2024 and only consider 10/12/2024. So sum of the balance from 1/1/2022 till 10/12/2024.- danextian
Super User
This is confusing. In your original post, you mentioned tha ytd should start from the beginning of the selected year up to today's date.
However, above you're telling that it should start from 1/1/2022.
Also, what would the end date be if todays' date was 10/12/2024 but the year selected was 2023?
- Jidnyasa2904
This worked for me:
ABC =
VAR enddate = CALCULATE(
MAX('Table'[Date]),
FILTER('Table', 'Table'[Year] = SELECTEDVALUE('Max Year'[Year]))
)
RETURN
CALCULATE(
SUM('Table'[Cost]),
'Table'[Date] <= enddate,
REMOVEFILTERS('Time Shift Table'), -- Remove all the tables which affect the date
REMOVEFILTERS('Table') -- In my case I had a Time shift table and a Date Table
)
Thanks!
