Calculate the sum for the last 3 days of data where amount > 0

[Anonymous]

Trying to sum amounts by company for the last 3 days that they had any amount. So if a company had amounts yesterday, 4 days ago, and 7 days ago I would want to only sum those amounts.  Creating a date rank almost solved this but since its ranking every day regardless of company, the lookback function below in my measure is missing data.

This is what the overall table looks like with all companies. 

Company		Amount		Date		date Rank
A		$          10		5/17/2021		91
B		$          16		5/16/2021		90
C		$          11		5/15/2021		89
C		$          13		5/14/2021		88
A		$          15		5/12/201		87
D		$            9		5/12/2021		87
D		$            9		5/10/2021		86
A		$            8		5/10/2021		86
A		$          12		5/1/2021		85

date rank = RANKX('Table1','Table 1'[Date],,ASC,Dense)  

Last 3 Days of Amount =

var _max = MAXX(allselected('Table 1'), 'Table 1'[date rank])

return

CALCULATE(SUM('Table 1'[Total Amount]),'Table 1',ALL('Table 1'),'Table 1'[date rank]>=_max-3)

The second formula would work if there was only one company in the dataset but since there are multiple companies then when it is filtered to company A for example the _max - 3 only goes back to the rank of 88 so it doesn't capture the amounts on 5/12 and 5/1 see below:

Company		Amount		Date		date Rank
A		$   10		5/17/2021		91
A		$   15		5/12/2021		87
A		$   12		5/1/2021		85

[Jihwan_Kim]

Hi, @Anonymous 

I tried to create a sample pbix file based on the explanation. I added some more random data for the company B and C to check.

Please check the below picture and the sample pbix file's link down below.

Amount total by value created recent 3 days =

VAR newtable =

ADDCOLUMNS (

SUMMARIZE ( 'Table', 'Table'[Company], 'Table'[Date] ),

"@recent3days",

SUMX (

KEEPFILTERS (

TOPN (

3,

CALCULATETABLE ( 'Table', ALL ( 'Table'[Date] ) ),

CALCULATE ( MAX ( 'Table'[Date] ) ), DESC

)

),

CALCULATE ( SUM ( 'Table'[Amount] ) )

)

)

RETURN

SUMX ( newtable, [@recent3days] )

https://www.dropbox.com/s/5ndsnkq95x9ozbl/skmathew.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak · Accepted solution]

@Anonymous , Try with these changes

//column

date rank = RANKX(filter('Table1', [Company] =earlier([Company])),'Table 1'[Date],,ASC,Dense)

//measure
Last 3 Days of Amount =
var _max = MAXX(filter(allselected('Table 1'),[Company] =max([Company])), 'Table 1'[date rank])
return
CALCULATE(SUM('Table 1'[Total Amount]),'Table 1',ALL('Table 1'),'Table 1'[date rank]>=_max-3)