topic Re: Calculate the sum for the last 3 days of data where amount > 0 in DAX Commands and Tips

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

Anonymous — Mon, 17 May 2021 21:58:27 GMT

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

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

amitchandak — Tue, 18 May 2021 02:31:38 GMT

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)

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

Jihwan_Kim — Tue, 18 May 2021 02:57:39 GMT

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 (

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