latest

6 Topics

Display the latest values by category based on a selected date
Hello all, First, as I am a french guy, I want to apologise in advance for my poor english ! Despite my searches since few days, I can not find the correct measure to solve my problem. I think I am close to the solution, but I really need help to achieve this job ! Here is my need : I have a dataset with a date table and a "Position" (ie "stock") table which is my fact table, with date column. Classic relationship between these 2 tables. Many Dates in "Position" table / 1 date un "Dates" table. My "Dates" table has a one date per day (Column "AsOf") My "Position" table looks like this : Id DealId AsOf Date Notional 10000 1 9/1/2022 2000000 10001 1 9/1/2022 3000000 10002 1 9/1/2022 1818147 10010 4 5/31/2022 2000000 10011 4 5/31/2022 997500 10012 4 5/31/2022 1500000 10013 4 5/31/2022 1127820 10014 5 7/27/2022 140000 10015 5 7/27/2022 210000 10016 5 7/27/2022 500000 10017 5 7/27/2022 750000 10018 5 7/27/2022 625000 10019 1 8/31/2022 2000000 10020 1 8/31/2022 3000000 10021 1 8/31/2022 1801257 10022 1 8/31/2022 96976 10023 1 8/31/2022 1193365 10024 1 8/31/2022 67883 Based on a selected date (slicer with all dates from "Dates" table), I would like to calculate the sum of Last Notional for each "Deal" (column "DealId"). So I must identify, for each Deal, the last "Asof Date" before or equal to the selected date, and sum all matching rows. Examples : If selected date is 9/1/2022, I will see all rows, except rows asof date = 8/31/2022 for deal 1 (as the last date for this deal is 9/1/2022). So I expect to see : DealId Sum of Notional 1 6 818 147 4 5 625 320 5 2 225 000 Grand Total 14 668 467 If I select 8/31/2022, total for Deal 1 changes (as we now take rows of 8/31 instead of 1/9) : DealId Sum of Notional 1 8 159 481 4 5 625 320 5 2 225 000 Grand Total 16 009 800 If I select 7/29, only deals 4 and 5 are active on this date, so the results should be : DealId Sum of Notional 4 5 625 320 5 2 225 000 Grand Total 7 850 320 I think I found a solution for the rows, but my total is wrong (only notionals of the selected date are totalized). I also think my measure is incorrect if I try to display the notional amounts aggregated by Rating (other column in my table) instead of deal. Here is my measure : Last Notional = VAR SelectedAsOf = SELECTEDVALUE(Dates[AsOf]) VAR LastAsofPerDeal = CALCULATE( MAX(Deals[AsOf Date]), FILTER( ALLEXCEPT( Deals, Deals[DealId] ), Deals[AsOf Date]<=SelectedAsOf ) ) RETURN CALCULATE( SUM(Deals[Notional]), FILTER( ALLEXCEPT( Deals, Deals[DealId] ), LastAsofPerDeal =Deals[AsOf Date] ) ) I hope it is clean for you, and you will be able to find a solution for this. Thanks in advance. Antoine
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1KViews
0likes
3Comments
Find Next Most Recent Date Dynamically
Hi, I'm struggling to find a solution in the forum for what I'm trying to accomplish, with that being said thank you and I appreciate any/all help to the resolution to (or guidance to a similar situation that may help resolve) my issue. I have a table similar to below PJB_ID Created_By Created_Date 1111 [email protected] 1/13/22 1112 [email protected] 2/21/22 1113 [email protected] 3/17/22 1114 [email protected] 2/5/22 1115 [email protected] 1/29/22 1116 [email protected] 4/5/22 1117 [email protected] 2/22/22 1118 [email protected] 3/24/22 The first thing I want to do is have a visual (table) that displays the most current PJB_ID the individual created something like this: PJB_ID Created_By Created_Date (most recent) 1117 [email protected] 2/22/22 1116 [email protected] 4/5/22 1118 [email protected] 3/24/22 Then if I have a date slicer and someone selects a date that is prior to the (most recent) created date, it will display the next most recent. So the table then would look like this if 2/1/22 was selected from the slicer: PJB_ID Created_By Created_Date 1115 [email protected] 1/29/22 1111 [email protected] 1/13/22 null [email protected] null I've tried to use the max, last date, and a few other functions but can't seem to get it to work the way I want. I apologize if this type of issue has been resolved already and if it has and someone can shoot me a link to the post, I'd greatly appreciate it. Thanks for giving this a look and helping. Regards, Cody
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.2KViews
0likes
6Comments
Identify latest result and count
What DAX, or combination of DAX, do I need to use to achieve the following please: I have a data source (excel based rn) which lists a project reference number, the date and the RAG status of that project. This data source is going to grow as each new months status updates are gathered together and added to the list. What I would like to do is have a measure for each RAG outcome which tots up the latest update - whether that's all the most recent months entries or from months ago (it could be that there are no updates for a few months on the trot). Data is organised in columns with reference numbers, date of update and RAG outcome (red, amber or green). In a matrix this looks like this: And you can see how some references have an updated RAG for every month, some have gaps and some have none. How do I count the most recent RAG statuses only (regardless of whether june or april for example), one time for each reference number only and for each RAG (3 sep measures)..?? I've tried other solutions to similar queries but relicating them hasn't worked and only given me the single most recent result.
Solved
laurenN
4 years ago Place DAX Commands and Tips
868Views
0likes
2Comments
Get latest value from other table that contains blank value
Hi, I need to return the latest value based on date of the entry. Table "Scoring Entry" consist of blank values and each test was submitted on different date and for different students. How am i supposed to get a table that looks like Table "Latest Scoring" that return only the latest scores for each students? I tried using FILTER, SUMMARIZE and LASTNONBLANKVALUE functions but that only return the result of MAX/MIN value of the scoring. Much Appreciated 🙂
Solved
abdmajidnor
4 years ago Place DAX Commands and Tips
1.1KViews
0likes
4Comments
Latest ID result on Matrix total values
Hello Power BI, I need to dsiplay the latest ID record from an specific column on the totals row. I have done this before with dates but this time I only have ID number. Thanks for your support.
Solved
Anonymous
4 years ago Place DAX Commands and Tips
811Views
1like
2Comments
DAX formula for latest month based on selected filters
Hi, I am trying to get the duration minutes for the lastest month of the selected months in a slicer. In case none is selected then it should consider the latest month/max. Currently I have the formula below, but it is clearly not working. Also DOWNTIME[MONTH] is a numeric field (1,2,3,...,12), so I would probably have to convert D_Calendar[Month] before evaluating. mDurationLatestSelectedMonths(min) = CALCULATE(sum(DOWNTIME[Duration Minutes]),KEEPFILTERS(DOWNTIME[MONTH]=VALUES(D_Calendar[Month]))) Thank you. Best regards, André
afbraga66
5 years ago Place DAX Commands and Tips
7.9KViews
0likes
17Comments