Cumulative sum - but only most recent one if duplicate is present

[Anonymous]

Hi guys,

I have a problem displaying data from my log file. It contains an ID, the date it got changed and a number in- / decreasing. The table looks like the following:

ID	Created	Changed_To
1	1.1.2020	5
2	1.1.2020	2
3	2.1.2020	3
2	3.1.2020	0
1	3.1.2020	10
1	4.1.2020	1
4	5.1.2020	2

My goal is to display this loghistory in a visual with dates/months as axis and sum those changes. The problem is that I need to sum those changes for each date and if the same ID has a new value it should take the most current one. Same should apply when I change the filter of the visual to monthly (if it is possible).

I.e for the dates displayed the corresponding values for the visual would be:

1.1.2020	2.1.2020	3.1.2020	4.1.2020	5.1.2020
5+2	5+2+3	0+3+10	0+3+1	0+3+1+2

I tried it with many summarize, group by cumulative sums, but I cant get a measure working that "deletes/overrides" the old value. I can only get the maxdate of all of them or calculate latest contribution but I dont know how to sum/del those in my visual.

I was so desperate I did it in python. If someone is able to translate this into dax/m or give me a hint what to do I would higly appriciate that.

import pandas as pd
import matplotlib.pyplot as plt 
import matplotlib.dates as mdates 
import datetime as dt

result = [] 
for currentdate in pd.date_range("2022-01-01", periods=365, freq="D")
    
    allUntilCurrentDay = dataset.loc[dataset['Created']<=currentdate]
    
    filteredValues = allUntilCurrentDay.sort_values('Created').drop_duplicates('ID', keep='last')

    sumOfChanged_To = filteredValues['Changed_To'].sum()

    if(currentdate<=pd.to_datetime('today')):
        result.append([currentdate,sumOfChanged_To])
    else:
        result.append([currentdate,0])

result_df = pd.DataFrame(data=result,columns=['Date','Sum_of_changes'])

[amitchandak]

@Anonymous , Try a measure like

calculate( sumx( Values(Table[ID), calculate(lastnonblankvalue(Table[Created]), Sum(Table[Changed_To]))), filter(allselected(Table), Table[ID] = max(Table[ID]) && Table[Created] <= max(Table[Created])))

[Anonymous]

@amitchandak 
Ok I tried your approach again and with a little tweak it works perfect! 

Measure = CALCULATE(
               SUMX(
                        VALUES('Table'[ID]),
                        CALCULATE(
                            LASTNONBLANKVALUE('Table'[Created],
                            SUM('Table'[Changed_to])
                            )
                        )
                    ),
               FILTER(ALLSELECTED('Table'),
//seemed to be not neccessary, ID is actually a String and max() doesnt work, is it ambigous? 
//*hopefully :D
//                         'Table'[ID] < max('Table'[ID]) && 

                           'Table'[Created] <= max('Table'[Created])))

Only problem is, that I'm missing days where no entry was made. Could you tell me where to put my calendar to get the sum of each of the days?

Although I have those connections in my model:
- Active: DimTable[ID] which is connected to the Table[ID] and DimTable[Date] to Calendar[Date]
- Inactive: connection between Table[Created] to Calendar[Date]

Sadly if I try to change the Relationship the measure still tries to use the first active one. My attempt was to add USERELATIONSHIP after the last FILTER like

...
FILTER(
  ALLSELECTED('Table'),
                     'Table'[Created] <= max('Table'[Created])),
USERELATIONSHIP(Calendar[Date],'Table'[Created])
)
// or to get each day
...
FILTER(
  ALLSELECTED('Table'),
                     'Table'[Created] <= max('Calendar'[Date])),
USERELATIONSHIP(Calendar[Date],'Table'[Created])
)

But the sums are not correct anymore and the date is the corresponding date from DimTable[Date]

Tyvm in advance!

[Anonymous]

Thank you for your fast reply! 

I might missed mentioning a few things, I'm sorry. 
It is actually possible dates are not present in the Table but I still need the sum of this missing date for the visual

• i.e. if we remove the 2.1.2020 from the table the visual should display the 2.1.2020 with a value of 5+2=7

I have an active Relationship between [Created] and a Calender[Date]. Can I use the Calender for determining each day and sum ?

Expected Output(python) - Current Output(dax)

[Anonymous]

CALCULATE(

               SUMX(

                        Table,

                        Table[Changed_To]),

               FILTER(ALL(Calendar),

                           Calendar[Date] <= max(Table[Created])))

This measure should do the trick for all dates right? Sadly I dont know how to implement it in your version to exclude those old duplicates