Forum Discussion
Make sum by ID using latest date
Hi
I am trying to create a visual, with the sum of values per ID over time. I want to show how the sum of values across all IDs has changed over time. If the ID is not found the actual date, the Value from the latest date for the given ID should be used in the sum.
My input data looks like this
| ID | Date | Value |
| 1 | 01-01-2022 | 4 |
| 3 | 15-01-2022 | 10 |
| 1 | 01-02-2022 | 8 |
| 2 | 02-02-2022 | 2 |
| 1 | 12-02-2022 | 10 |
| 2 | 12-02-2022 | 4 |
| 3 | 15-02-2022 | 5 |
| 1 | 01-03-2022 | 1 |
| 2 | 01-04-2022 | 0 |
I expect the output to look like so:
| Date | Sum of Value |
| 01-01-2022 | 4 |
| 15-01-2022 | 14 |
| 01-02-2022 | 18 |
| 02-02-2022 | 20 |
| 12-02-2022 | 24 |
| 15-02-2022 | 19 |
| 01-03-2022 | 10 |
| 01-04-2022 | 6 |
I would assume that I need to use a measure, as I need to use the result in other calculations.
Let me know if anything needs to be clarified.
Thank you in advance!
Hi Athystrup ,
Please create a measure with below dax formula
Measure = VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) VAR tmp1 = FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date && 'Table'[ID] = 1 ) VAR tmp2 = FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date && 'Table'[ID] = 2 ) VAR tmp3 = FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date && 'Table'[ID] = 3 ) VAR _lastdate1 = MAXX ( tmp1, [Date] ) VAR _lastdate2 = MAXX ( tmp2, [Date] ) VAR _lastdate3 = MAXX ( tmp3, [Date] ) VAR _1 = CALCULATE ( MAX ( 'Table'[Value] ), 'Table'[Date] = _lastdate1, 'Table'[ID] = 1 ) VAR _2 = CALCULATE ( MAX ( 'Table'[Value] ), 'Table'[Date] = _lastdate2, 'Table'[ID] = 2 ) VAR _3 = CALCULATE ( MAX ( 'Table'[Value] ), 'Table'[Date] = _lastdate3, 'Table'[ID] = 3 ) RETURN _1 + _2 + _3Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Athystrup ,
Please create a measure with below dax formula
Measure = VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) VAR tmp1 = FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date && 'Table'[ID] = 1 ) VAR tmp2 = FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date && 'Table'[ID] = 2 ) VAR tmp3 = FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date && 'Table'[ID] = 3 ) VAR _lastdate1 = MAXX ( tmp1, [Date] ) VAR _lastdate2 = MAXX ( tmp2, [Date] ) VAR _lastdate3 = MAXX ( tmp3, [Date] ) VAR _1 = CALCULATE ( MAX ( 'Table'[Value] ), 'Table'[Date] = _lastdate1, 'Table'[ID] = 1 ) VAR _2 = CALCULATE ( MAX ( 'Table'[Value] ), 'Table'[Date] = _lastdate2, 'Table'[ID] = 2 ) VAR _3 = CALCULATE ( MAX ( 'Table'[Value] ), 'Table'[Date] = _lastdate3, 'Table'[ID] = 3 ) RETURN _1 + _2 + _3Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you for taking the time to answer.
What would I do if I had N IDs? The data displayed above is just a subset. In my actual dataset there are over 200 unique IDs, so is there any way the measure can be changed, so that it sums dynamically?Hi Athystrup ,
Sorry, dynamic calculations may not be possible, limited by the calculation logic.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
