Cumulative/Running total for financial year

[d904490]

I have 3 different tables as presented below:

Target , Date, Actual and the excel sheet is the expected result what I'm trying to create. Need quick help how can I do it, I'm very new to power bi, so not able to understand do, I need to create some relationships among the table or can be done without. 

 

[d904490]

I'm replying to myown post. I read through various solutions on runnung total and tried to excute. I came up with solution but it's not looking exactly correct. can anyone help how to fix it. I have created a relationship between the date column of taregts table and date column of actuals table using this measure RunningTotal = CALCULATE(Sum('Actual'[TotalAmount]),FILTER(ALLEXCEPT('Actual','Actual'[TotalAmount]), 'Actual'[TotalAmount] <= MAX(Target[DateDay])))

this is the output i got from this measure - 

It's giving me the result from sep 2018 but I have the data from Aug 2018, so it should give the result from Aug not Sep. It might be some minor fix, any quick help is much appreciated. Thanks.

[v-yulgu-msft]

Hi @d904490,

Please try this measure:

RunningTotal = CALCULATE(Sum('Actual'[TotalAmount]),FILTER(ALLSELETED('Actual'), 'Actual'[ColumnDate] <= MAX(Target[DateDay])))

Regards,

Yuliana Gu

[d904490]

Thanks Yuliana. But this measure is not giving the blank rows. Table is not showing any values in the Running Total column. The table realtionship is still between Actuals[Date] and Target[DateDay].

[v-yulgu-msft]

Hi @d904490,

Is the highlighted column your desired result?

But could you please provide more description about how to calculate these values? For example, I can understand the total for 2018-August is 5330, but, for 2018-September, why is only 100 added?

Best regards,

Yuliana Gu

[d904490]

Yes, the highlighed column is my desired result. Apologies, I forgot to update the excel after the database has been updated, yes 160 should now be the number of actuals achieved in september for now, if some more tasks will be performed during the month this number will increase and the same process should keep going for the next months, as I am using the data from the database. Please check the below image, I have updated the excel sheet again, to make it more understandable.