Dynamic date range slicer with only the max date (end date) selected and ignoring the min date

[Jidnyasa2904]

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 

[Jidnyasa2904 · Accepted solution]

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!

[Vijay_Chethan]

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]

 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]

Sorry for the confusion. The Start date should always be the first available date in the table; in my case, it is 1/1/2022. 
Assuming today's date is 10/12/2024
If I select the year as 2023 and the time shift filter as YTD, the start date should be 1/1/2022, and the end date should be 10/12/2023. 
I have a custom date column that adjusts per the current year. 
I am looking for a way to ignore the start date of any filter selected and consider just the end date. 

[danextian]

You can try:

VAR _end =
    CALCULATE ( LASTNONBLANK ( Dates[Date], [Measure] ), ALLSELECTED ( Dates ) )
RETURN
    IF (
        SELECTEDVALUE ( TimeShift[Value] ) = "YTD",
        CALCULATE ( [Measure], FILTER ( ALL ( Dates ), Dates[Date] <= _end ) )
    )

_end checks for the latest date with value in the current context. You mean replace the expression forr the variable with TODAY().