Reply
Jidnyasa2904
Helper I
Helper I
Partially syndicated - Outbound

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


1 ACCEPTED SOLUTION
Jidnyasa2904
Helper I
Helper I

Syndicated - Outbound

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!

View solution in original post

6 REPLIES 6
Jidnyasa2904
Helper I
Helper I

Syndicated - Outbound

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

Syndicated - Outbound

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.

Syndicated - Outbound

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.

Syndicated - Outbound

 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.

danextian_0-1741697702703.png

 

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?





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Syndicated - Outbound

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. 




Syndicated - Outbound

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





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)