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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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 ?

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors