historical data
3 Topics
Calculating the Monthly Total at a Specific Point in Time
I'm struggling to solve this tricky "point in time" calculation. I have 4 columns in the 'Opportunity History' table. 1. OpportunityId 2. CreatedDate 3. CloseDate 4. Loan Amount I also have a 'Date' table. Each OpportunityId has multiple records, meaning the OpportunityId is not a unique identifier. Each time the CloseDate changes, a new record is created. The CreatedDate column tracks the date of change of the CloseDate, and the CloseDate column tracks the date of each new close date. The Loan Amount column has the same value for all records for each OpportunityId. I want to create a measure that equals the total loan amount (sum) that is closing in the given month at a specific point in time. For example, if I select 12/1/2024 in my slicer, I want the output to be the sum of Loan Amount of all OpportunityIds with a CloseDate equal to or before 12/31/2024 at that point in time. I want to make sure it includes all OpportunityIds with their most recent CreatedDates being before 12/1/2024, even if the most recent CreatedDate was 11/1/2024 with a CloseDate of 12/31/2024. However, if on 12/2/2024 the CloseDate changed to 1/1/2025, then I would not expect the output to include the sum of this OpportunityId when 12/2/2024 is selected in the slicer. If on 12/3/2024 the CloseDate changes back to 12/31/2024, then I would expect the output to include the sum of this OpportunityId when 12/3/2024 is selected in the slicer. I'm also not sure if the relationship between my two tables should use CreatedDate or CloseDate. All help is greatly appreciated!Solved1.2KViews2likes5Comments
DAX measure: ID from MIN date - historical data in live connection
Hello all, This is a follow-up question from my last post: DAX measure: MIN date for historical data in live connection I have a live connection to a SQL table “Data” with historical data. Example Data: ID Status Start End Changed 1 A 2022-05-13 2022-05-13 1 B 2022-05-13 2022-05-14 2 A 2022-05-15 2022-05-15 3 A 2022-05-15 2022-05-15 2 C 2022-05-15 2022-05-16 2022-05-16 3 B 2022-05-15 2022-05-16 3 B 2022-05-15 2022-05-17 3 C 2022-05-15 2022-05-18 2022-05-18 4 A 2022-05-15 2022-05-15 4 A 2022-05-15 2022-05-16 5 A 2022-05-15 2022-05-16 6 A 2022-05-16 2022-05-16 I get the MIN Start-date with Status “A” if the corresponding ID had no other Status since then according to the following formula (all thanks to Jihwan_Kim): VAR _IDunderA = SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] ) VAR _IDunderothers = SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] ) VAR _IDonlyA = EXCEPT ( _IDunderA, _IDunderothers ) VAR _newtable = CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) ) RETURN MINX ( _newtable, Data[Start] ) Result for example data: 2022-05-15 What to achieve: Now, I would like to get the corresponding ID or IDs to the MIN Start-date respectively. Expected Result: ID 4 ID 5 I tried a combination of FILTER and SELECTEDVALUE but it did not work so far. VAR _IDunderA = SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] ) VAR _IDunderothers = SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] ) VAR _IDonlyA = EXCEPT ( _IDunderA, _IDunderothers ) VAR _newtable = CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) ) RETURN CALCULATE(SELECTEDVALUE(Data[ID]), FILTER(Data,Data[Start]=MINX( _newtable, Data[Start]))) Any suggestions? Thanks!Solved589Views0likes1CommentDAX measure: MIN date for historical data in live connection
Hello all, I have a live connection to a SQL table “table1” with historical data. Example Data: ID Status Start End Changed 1 A 2022-05-13 2022-05-13 1 B 2022-05-13 2022-05-14 2 A 2022-05-15 2022-05-15 3 A 2022-05-15 2022-05-15 2 C 2022-05-15 2022-05-16 2022-05-16 3 B 2022-05-15 2022-05-16 3 B 2022-05-15 2022-05-17 3 C 2022-05-15 2022-05-18 2022-05-18 4 A 2022-05-15 2022-05-15 4 A 2022-05-15 2022-05-16 5 A 2022-05-16 2022-05-16 What to achieve: Now, I would like to get the MIN Start-date with Status “A” if the corresponding ID had no other Status since then. Expected Result: The output regarding the example table would be 2022-05-15. Solution: ID 4 and ID 5 are the only IDs that did not switch their state. ID 4 provides an earlier start date then ID 5. I tried different approaches, like comparing a subset of Status A with a subset of other statuses to get the IDs that are in subset A but not in the other one. Another means could be an index if it would be possible to calculate a column. However, since it is a live connection, I cannot calculate new tables or columns. The DAX measure has to be calculated without those intermediate steps. A change of the connection type is not possible. I appreciate your help! Thanks.Solved1.1KViews0likes2Comments