Forum Discussion

Jidnyasa2904's avatar
Jidnyasa2904
Icon for Helper I rankHelper I
1 year ago
Solved

Dynamic 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 Filte...
Need Help
  • Jidnyasa2904's avatar
    Jidnyasa2904
    1 year ago

    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!

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

 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's avatar
Jidnyasa2904
Icon for Helper I rankHelper I
1 year ago

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's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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().