Multiple filters for Calculate and SUM

[jeyare]

Hi Gents,

I have issue with multiple filter for CALCULATE and SUM of "Sales" column. Here is a simple table:

Date	Branch	Sales	SUM
7.1.2020	A	10	10
7.1.2020	B	12	12
7.1.2020	C	15	15
7.1.2020	D	30	30
8.1.2020	A	5	15
8.1.2020	B	4	16
8.1.2020	C	7	22
8.1.2020	D	14	44
9.1.2020	A	10	25
9.1.2020	B	12	28
9.1.2020	C	15	37
9.1.2020	D	30	74
10.1.2020	A	5	20
10.1.2020	B	4	20
10.1.2020	C	7	29
10.1.2020	D	14	58
11.1.2020	A	10	25
11.1.2020	B	12	28
11.1.2020	C	15	37
11.1.2020	D	30	74
12.1.2020	A	5	20
12.1.2020	B	4	20
12.1.2020	C	7	29
12.1.2020	D	14	58

Challenge:

1. I need calculate SUM of (Sales column) by last "n-days" condition but in reverse order = from last possible date to earliest days. E.g. Today is Jan 12th, then "n-days" order is started in this order:

1st day is JAN 12th

2nd day is JAN 11th

3rd day is JAN 10th ....

2. I need take into account the Branch ID (Branch column), then I need calculate "n-days" sum for each Branch independently

3. Final SUM column

- I need take into account, that last "n-days" in the earliest rows will provide error, becouse it will be < than "n-days"

- last recorded DATE (frequently TODAY) may be different from TODAY (), then needs to be detected such DATE value

Thx for a help!

[littlemojopuppy · Accepted solution]

Hi @jeyare 

Your question did not specify how many days, so I'm assuming 10.  If you need a different number, change it in the DATESINPERIOD function below.

Total Branch Sales Last 10 Days:=CALCULATE(
		[Total Sales],
		DATESINPERIOD(
			'Calendar'[Date],
			LASTDATE('Calendar'[Date]),
			-10,
			DAY
		),
		VALUES(Table1[Branch])
	)

 If what you're looking for is all possible dates before most recent date (unclear from your post), use this instead

Total Branch Sales Running Total:=CALCULATE(
		[Total Sales],
		FILTER(
			ALL('Calendar'),
			'Calendar'[Date] <= MAX('Calendar'[Date])
		),
		VALUES(Table1[Branch])
	)

Here's a screen snip showing the results of both calculations...

Hope this helps!  🙂

[jeyare]

thx, great

[littlemojopuppy]

De nada.  Glad I could help!  🙂

[amitchandak]

@jeyare , with help from a date table you should able to get a measure like

Rolling 12 = CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,Day))

here n =12.

Can you share expected output ?

[jeyare]

Thx

understan what is DATESINPERIOD, in this case:

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

<dates> .... the date column ... OK

<start_date>  .... the Max value of the Date column

<number_of_intervals> .... reverse order for negative number

<interval> ... in Days

But:

result of your code is sum of the current day

[amitchandak]

@jeyare , You are using date from the date table in formula and display(Visual) ?