Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jeyare
Helper II
Helper II

Multiple filters for Calculate and SUM

Hi Gents,

 

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

DateBranchSalesSUM
7.1.2020A1010
7.1.2020B1212
7.1.2020C1515
7.1.2020D3030
8.1.2020A515
8.1.2020B416
8.1.2020C722
8.1.2020D1444
9.1.2020A1025
9.1.2020B1228
9.1.2020C1537
9.1.2020D3074
10.1.2020A520
10.1.2020B420
10.1.2020C729
10.1.2020D1458
11.1.2020A1025
11.1.2020B1228
11.1.2020C1537
11.1.2020D3074
12.1.2020A520
12.1.2020B420
12.1.2020C729
12.1.2020D1458

 

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!

1 ACCEPTED SOLUTION
littlemojopuppy
Community Champion
Community Champion

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...

littlemojopuppy_0-1610115600775.png

 

Hope this helps!  🙂

View solution in original post

6 REPLIES 6
littlemojopuppy
Community Champion
Community Champion

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...

littlemojopuppy_0-1610115600775.png

 

Hope this helps!  🙂

thx, great

De nada.  Glad I could help!  🙂

amitchandak
Super User
Super User

@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 ?

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.