cancel
Showing results 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

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:

 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!

1 ACCEPTED SOLUTION
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...

Hope this helps!  🙂

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

Hope this helps!  🙂

Helper II

thx, great

Community Champion

De nada.  Glad I could help!  🙂

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 ?

Helper II

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors