date filter

10 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
date filter not working in QA_KPI_PROJ
date filter is not working for all three report under QA_KPI_PROJ. if we select date range, it not generating date as per selected date.
shailendravyas
2 years ago Place Report Server
373Views
0likes
1Comment
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
951Views
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
Two Date Slicer for two different matrix
Hello Community, I have a question regarding the use of date slicer/filter. My initial situation is very simple: I have a table with our firms revenue at each day of the year. Now I want to create two matrix where two different months can be compared. For example: On the 1st slicer/filter I choose may to view the revenue on each day of may in the 1st matrix. On the 2nd slicer/filter I choose august to view the revenue on each day of august in the 2nd matrix to compare it with the first. I think the problem seems quite simple and common to everyone who wants to compare two different month but I didn't found the solution in the community so far. It would be really nice of you, if you clould give me some hints how to solve the problem. If you have any question or need further information regarding this problem just let me know. Best regards Julius
Anonymous
5 years ago Place Desktop
868Views
0likes
1Comment
Report for Opportunities closing in 0-30 days, 31-60 days, 61-90 days & > 90 days
Hello Everyone, I just started using Power Bi but I have some experience with it, not as much as id like. I built out a dynamics CRM for sales and I decided to make weekly sales reports in Power Bi rather than SSRS-BIDS SQL reporting. I have created a sample page where I have 3 tables stacked on top of each other. It reads like: Opportunities Closing in the Next 30 Days currently shows all opportunities from today's date with [est. close date] "is in the next" 30 days Opportunities Closing in the Next 60 Days currently shows all opportunities from today's date with [est. close date] "is in the next" 60 days shows duplicates of today- day 30, then new after 31-60 Opportuntites Closing in the Next 90 Days currently shows all opportunities from today's date with [est. close date] "is in the next" 90 days shows duplicates of today- day 60, then new after 61-90 I am using the following fields in each table [Topic] [Account] [Est. Close Date] [Pipeline Phase] [Owner] [Date of Last Activity] [Est Revenue] I want each table to show Opportunities without the duplicates. Sort of like binning data. Im looking to display opportunities closing in (0-30)(31-60)(61-90) days not (0-30)(0-60)(0-90) with three different tables. Where do I start? Do I need to do this with code since the filters in power bi don't give me ability to select [est. close date] after X days and before Y days? It seems like this should be relatively easy. Thank you so much for your help. All the best, Josh
Dribblej
5 years ago Place Desktop
1.6KViews
0likes
1Comment