date filter

7 Topics

Filter By Date Relationship
In the model if I select 'Quota Results'[Location Quota] and 'Current Hierarchy 2'[Sales Code] and then put a filter in the report for 'Calendar 2'[Month Year]October 2024, it correctly shows the Sales Codes and Location Quota for the month. But I've added another table that has the fields 'BCP'[UniqueID] and 'BCP'[Date Closed] among others. UniqueID is equivilant to Sales Code. I created a table using DAX: Table Location Quota = SELECTCOLUMNS( 'Current Hierarchy 2', "SalesCode", [Sales Code], "LocQuota", [Location Quota]) I then created relationships between 'BCP'[UniqueID] and 'Current Hierarchy'[Sales Code] and relationship 'BCP'[Date Closed] and 'Calendar 2' [Date]. Now when I select "SalesCode" and "LocQuota" I get individual totals for the location. But the LocQuota is summing the quota for several years instead of the month of when location was closed in 'BCP'. I've tried numerous measures but I get the same grand total of all the locations quota in each row. Any insight into how to filter it for the month would be greatly appreciated. BTW Location Quota is determined by Month whereas Date Closed is a date like Friday, October 2, 2024, so I even created a measure StartofMonth=STARTOFMONTH('BCP'[DateClosed]) and then a measure: QuotaDateFilter = CALCULATE([SumLocQuota],FILTER(All('Calendar 2'),'Calendar 2'[Date]=('BCP (2)'[StartofMonth]))) It takes up too much resource or other measures I've tried put the grand total of all the locations in each row. Please help.
Solved
BrianNeedsHelp
1 year ago Place DAX Commands and Tips
980Views
0likes
2Comments
Table Date Filter
I am working on creating a filtered table based on date ranges defined by a slicer value. From the Slicer I determine the [FilterStartDate] and [FilterEndDate] in the measures at the bottom. When I create the new table I'm just trying to take the values [Est. Ship Date] from the table in the defined date range. I have tried to use DatesBetween but end up with similar results. If I replace [FilterStartDate] and [FilterEndDate] with Date(yyyy, mm, dd) the filter works fine but when I put the measure in it does not work. It appears I'm having some kind of a format issue but have not been able to find a resolution. Any thoughts are appriciated. -------------------------------------------------------------------------------------------------------------------------------------- MC_OrdersDateFilter = FILTER( _MC_Orders, ( DATEVALUE(_MC_Orders[Est. Ship Date]) >= DATEVALUE([FilterStartDate]) && Datevalue( _MC_Orders[Est. Ship Date]) <= DATEVALUE([FilterEndDate]) ) ) ------------------------------------------------------------------------------------------------------------------- FilterStartDate = var TodayDate = Today() var Wk12Date = CALCULATE(FIRSTDATE(DateRange[12Week]), DateRange[IsToday] = TRUE) var Wk12MonthEnd = Calculate(ENDOFMONTH(DateRange[CalendarDate]), DateRange[Year] = Year(Wk12Date), DateRange[MonthNumber] = Month(Wk12Date)) var YearStart = Calculate(STARTOFYEAR(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate) ) var YearEnd = Calculate(ENDOFYEAR(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate) ) var QtrStart = Calculate(STARTOFQUARTER(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[Quarter] = QUARTER(TodayDate)) var QtrEnd = Calculate(ENDOFQUARTER(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[Quarter] = QUARTER(TodayDate)) var MonthStart = Calculate(STARTOFMONTH(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate)) var MonthEnd = Calculate(ENDOFMONTH(DateRange[CalendarDate]), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate)) var NextMonthStart = Calculate(STARTOFMONTH(NEXTMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate)) var NextMonthEnd = Calculate(ENDOFMONTH(NEXTMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate)) var PrevMonthStart = Calculate(STARTOFMONTH(PREVIOUSMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate)) var PrevMonthEnd = Calculate(ENDOFMONTH(PREVIOUSMONTH(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate), DateRange[MonthNumber] = Month(TodayDate)) var NextYearStart = Calculate(STARTOFMONTH(NEXTYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate)) var NextYearEnd = Calculate(ENDOFMONTH(NEXTYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate)) var PrevYearStart = Calculate(STARTOFMONTH(PREVIOUSYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate)) var PrevYearEnd = Calculate(ENDOFMONTH(PREVIOUSYEAR(DateRange[CalendarDate])), DateRange[Year] = Year(TodayDate)) var Result = Switch( True(), SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "This Yr", YearStart, SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "This Mnth", MonthStart, SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Prev Yr", PrevYearStart, SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Next Yr", NextYearStart, SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Next Mnth", NextMonthStart, SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "Prev Mnth", PrevMonthStart, SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "12 Wk", MonthStart, SELECTEDVALUE('MTD/QTD/YTD Selection'[Selection]) = "YTD", YearStart ) Return DATEVALUE(Result) //Date(2023, 01,01)
shippen70
3 years ago Place DAX Commands and Tips
952Views
0likes
3Comments
Card Graph that will show top 1, 3,and 5 based on a category with a date filter
Hello, I have a table that looks like this: Report Date Country Amount 09/31/2021 Country1 1000 09/31/2021 Country1 2000 09/31/2021 Country2 3000 09/31/2021 Country2 1000 09/31/2021 Country3 5000 09/31/2021 Country3 1000 09/31/2021 Country4 7000 09/31/2021 Country4 8000 09/31/2021 Country5 1000 09/31/2021 Country5 1000 07/30/2021 Country1 5000 07/30/2021 Country1 1000 07/30/2021 Country2 7000 07/30/2021 Country2 8000 07/30/2021 Country3 1000 07/30/2021 Country3 1000 07/30/2021 Country4 1000 07/30/2021 Country4 2000 07/30/2021 Country5 3000 07/30/2021 Country5 1000 Report Date 09/31/2021 Country1 3000 Country2 4000 Country3 6000 Country4 15000 Country5 2000 I would like to create individual card graphs that will show the top 1 (Country4), 3 (Country3) and 5 Country5). I'm also using a Date Slicer to check out whos the top 5 in previous reports. The country entries spans up to more than 20 so its important to get the top 1, 3 and 5. I thought i can get away with using the Filters on this Visual option but it only allows us to use one filter for the Country column. Thanks in advance!
Solved
delithyotho
4 years ago Place DAX Commands and Tips
3.6KViews
0likes
3Comments
Quarterly Target to reset every quarter
I currently have my quarterly targer set at 6. For me to get a daily target value in the case I selected a particular date, it gives me what I want. However I need the target value to reset every quarter. Below is what I have as my solution Assuming I have a fixed expected quarterly target of 6. This is how I want the table broken down if there is a date selection with their respective quarters. Quarter Date Target Q1 2022 1/7/2022 0.47 Q1 2022 3/25/2022 5.67 Q1 2022 3/31/2022 6.00 Q2 2022 4/8/2022 0.67 However, When I have it selected as a quarter, I get the numbers compounded like this. I get 6.07 when I select just Q2 2022 since it is taking 1st day of Q2 2022 which is 4/1/2022. Quarter Target Q1 2022 6.00 Q2 2022 6.07 Expected Result: I want the target valies to reset every quarter so that it compounds to 6 by the end of every quarter. Quarter Target Q1 2022 6.00 Q2 2022 0.07 Thank you for your help.
Solved
yve214
4 years ago Place DAX Commands and Tips
843Views
0likes
2Comments
Can't get the recent Date Values
Hi there, I am trying to get the recent date value for the table below. I have tried the max and lastdate function but I cant seem to get the results i am expecting. I was able to get a seperate latest effective date column with Measure = Calculate(MAX(Table(Effective_Date), Allexcept(Table, table(id)) Table: ID Emp_ID Interval Effective_Date Score 1 1001 0 01/01/2020 3.1 2 1001 0 08/26/2020 3.3 3 1001 3 11/20/2020 3.5 4 1001 5 05/05/2020 3.2 5 1001 5 2/1/2021 4.1 6 1002 0 1/1/2022 2.0 Expected result if i want to see the recent date values if i filter by interval 0. ID Emp_ID Effective_Date Score 2 1001 8/26/2020 3.3 6 1002 1/1/2022 2.0 or if i dont filter any of the intervals i should get all client IDs with a recent date value witht their corresponding intervals. ID Emp_ID Interval Effective_Date Score 2 1001 0 8/26/2020 3.3 6 1002 0 1/1/2022 2.0 3 1001 3 11/20/2020 3.5 5 1001 5 2/1/2021 4.1
Solved
yve214
4 years ago Place DAX Commands and Tips
2.9KViews
0likes
11Comments
How To Change or Replace Date Context of Measure
This is what my data table looks like. Date Qty On Hand Qty Expired Qty Sold Qty Available Mar 2021 1000 200 300 500 Apr 2021 300 200 May 2021 150 400 Qty On Hand is from an Inventory Table. Qty On Hand = Calculate(SUM(Inventory), Date=Mar 2021) Qty Expired and Qty Sold numerical values. Qty Available is calculated as: QTY Available = Calculate([Qty On Hand]-([Qty Expired] + [Qty Sold])) How do I remove the Date Context from the Qty on Hand measure so that I can use it in the QTY Available calculation for the future months?
CEllinger
5 years ago Place DAX Commands and Tips
1.6KViews
0likes
1Comment
Filter month year column to exclude past dates from slicer
I have a dates key table that runs from Jan 2018 - Jan 2021. I have a month year column (Text Format) so it doesn't display data past today: On or Before Today = 'DimDate'[DateS] <= EOMONTH(TODAY(),0) Is there a way to have a slicer's past dates respected when it changes? e.g. When I slice the last 24 months I get this However when I slice the last 2 months I still get the begining dates of slicer: Something along the lines of this??? On or Before Today = ALLSELECTED('DimDate Sort'[Dates]) >= 'DimDate'[DateS] <= EOMONTH(TODAY(),0) Thanks in advance *CAVEAT - I have a DimDate table for counts and a DimDate Sort to build relationships off in case you're wondering
Anonymous
5 years ago Place DAX Commands and Tips
1.6KViews
0likes
4Comments