Filter a DAX measure with dynamic date filters / Time Intelligence
I need to filter a calculated table to find the metric for a particular department of goods and a dynamic date range. The Sales Cycle should have a Sold % Quantity which is the total items sold divided by the number of items produced. These two numbers need to match: This DAX is not working: Sold % Qty Clothing = VAR _startSalesCycle = DATE(2024, 02, 04) VAR _endSalesCycle = DATE(2024, 02, 27) CALCULATE( [Sold % Qty], 'Production Units by Week'[DepartmentName] = "Donated Clothing", DATESINPERIOD(Dates[Date], _startSalesCycle, _endSalesCycle, DAY ) ) Eventually, I want the start and end of the Sales Cycle to determine how this table gets filtered on a weekly basis. Which date filters should I use?
Date Filtering - +2 months from current date without including all months in between
Hi There, I have 3 pages where each of the pages should show only the specific values for "this month", "next month", "+2 month". Example for this month: Page 1: Shows only values for May 2022 ("this month") Page 2: Shows only values for June 2022 ("next month") Page 3: Shows only values for July 202 ("+2 month") I have managed the first two pages with the relative date filter. I have a problem with the third page, because if I make it with a relative filter, it includes the values for June AND July, as below and I only want to see July. Below is an example of the cards I am using to show this data. Below are the related fields for baselines that I am using to attempt to get this data, both on the "DBeaver Connection" table. Baseline #'s = MS110 Baseline #'s (whole number field) Dates = MS110 (B) (date field) Any help would be appreciated!
Dateslicer controlled by a measure filter
Hi All Hope someone can help. This Is bugging the heck out of me. Examble images at the end of the post. Link to onedrive file - hope this works In order to enhance the UI I am trying to create a filter where from where the user can specify set intervals of the past 35 days, the past 60 days and the past 420 days (these are just exambles). When the filter is selected the date slicer on the page must be filtered accordingly. So if no filter is selected the entire period is available and if for instance 'offset -35' is selected the date slicer is limited to days tat are no less than 35 days ago as well. I figured I would add a binary filter to my date slicer – 1 or 0 based and a measure that produces 1 if the value is larger than the specified offset number i.e. -35 else 0. See Image1. Below I have a measure – DateFilter – based on a disconnected table (image2). It outputs either 1 or 0 relative to the offset value in the Dimension date table. This seemed to work when I tested in a table and when I set is up as a filter LIST. When I select -35 DateFilter returns 1 for the desired period and 0 before. See image3 and Image4. But when set as a slicer it doesn’t work. The slicer is can't be modified and just shows dd-mm-yyyy. See image5. I am guessing it’s because the slicer doesn’t have a selected value. Any Ideas as to how I solve this? A big Thank You in advance. Best regards Kasper Datefilter = VAR SelectDaysOffset = SELECTEDVALUE ( D_Periodfilter[DaysOffset] ) VAR Datefilter = SWITCH ( TRUE (), SELECTEDVALUE ( D_Datotable[OffsetDate] )> SelectDaysOffset, 1, 0 ) RETURN Datefilter Image1 Image2 Image3 Image4 Image5Relative date slicer... Again.
Hi, Ive been playing around trying to find a solution for the crazy relative date slicer. Ive built a warehouse table with dates from -10 to +10 years and have included DST offset and UTC time. The thinking was that I link my local date field to this table and get back a UTC time that I can use in the slicer taking into account DST. The model works great but Ive uncovered another issue with this stupid slicer... It seems when you select "Last 1 days" that it will select based on date, not time!! It will select everything where the date is yesterday, not simply the last 24 hours. Can anyone else verify this? Has anyone got a solution (*without code* because i need this to be self-service) for using a slicer to select relative dates that takes into account timezones and DST?
Date Slicers and Relative Date filtering - Allowing users to updates dates beyond date bounds
Hi - I'd like to default date slicers to last month (updating each month automatically) but allow users to change the dates if necessary. Adding a relative date filter to that slicer accomplishes the first part but it seems that it doesn't allow the user to change the dates to anything outside the dates selected in the filter. .e.g I set the relative date filter to last month. The date slicer that defaults to 12/1/17-12/31/17. I'm unable to update the date filters to anything before 12/1/17 or anything after 12/31/17 (only in between). Is there a way to accomplish this? Thanks, S
