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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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 ?

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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