Sum of remaining SP until a specific date

[darkobrown]

So I need to create a burndown report. 

The problem I have is that the SUM does not work as I want it to. 
I have 2 tables: 

change_history_SP:

Here the changes in the fields are stored. 

and a Datetable:

here is the formula I use to get the sum for 'Remaining SP' :

Remaining SP = 
CALCULATE (
        SUM(change_history_SP[Only1ValueperDay Remaining Sp]),
        FILTER (
            ALL ( change_history_SP ),
            change_history_SP[created] <= EARLIER ( [Date] )
        )
)

As you can see in the change_history_SP table on april 25,2023 the value changes from "31" to "23" 

What I want to happen in the table 'Date' in the Column 'Remaining SP' that until April 25 in every day it says 31. And on april 25,2023 it should change to 23. What happens instead is it calculated 31 + 23 and so after april 25,2023 it says  54. 
How can I achieve that it says 23 after april 25,2023? 

[v-eqin-msft]

Hi @darkobrown , 

Here are the steps you can follow： 

1. Create calculated column. 

IF =  
IF( 
   'Table'[Date] in SELECTCOLUMNS('change_history_SP',"Date",[Created]) , 
1,0) 

Group =  
SUMX( 
    FILTER(ALL('Table'), 
    'Table'[Date]<=EARLIER('Table'[Date])),[IF]) 

Flag_Value =  
var _value= SUMX( 
       FILTER(ALL(change_history_SP), 
       'change_history_SP'[Created]=EARLIER('Table'[Date])),[Only1ValueperDay Remaining Sp]) 
return 
IF( 
    _value<>BLANK(),_value,0) 

Column =  
SUMX( 
    FILTER(ALL('Table'), 
    'Table'[Group]=EARLIER('Table'[Group])&&'Table'[Date]<=EARLIER('Table'[Date])),[Flag_Value]) 

 2. Result: 

Best Regards, 

Liu Yang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly 

[darkobrown]

I did all that but in my case i get every 0 in all fields... 

Do you have any idea where it may failed? 

BR,

Darko