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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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 ?

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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