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

Frequent Visitor

## Calculating sums based on multiple slicers (parameters)

Hi

I need to calculate the sum of Values based on the parameters shown on the attached file. The slicers are shown on the left side (see below).

The 2 date fields are inputs based on the [Proj_Create_Date].

Week Diff is the difference between the two selected dates

Future_Weeks is a drop down select list based on the [Projected_Week]

In PBI:

I have unpivoted the week values as shown in the blue table.

Also created Min and Max measures to calculate the [Week_Diff] using DateDiff function.

[Future Weeks] is a drop down list of the [Projected_Week].

Added a [Proj_Week_Date] which shows the actual date of the week based on the [Projected_Week] number

The requirements are:

- calculate the sum of the value of an item,

based on the selected [First Week] and the [Week_Diff] value. So if the selected 2 days are as shown above then the week diff is 2 weeks. Then, I need to sum the values in 14/07/2024 (First Week selected) line for week numbers 1 and 2.

Eg1: so if we take Item number 2 as an example, it should be 140.

Eg2: If the date parameters were set to [First Week] = 07/07/2024 and [Last Week] = 21/07/2024, [Week_Diff] is 3.

Then for Item number 2 the result should be 70 (sum of week (1, 2, and 3).

- Calculate the sum of an item

Same logic as above, but now also add the extra weeks based on the [Future Weeks] selected. So as in the above Eg1, add another 5 weeks (as that's what's selected) to the 2 weeks' result (sum of 7 weeks).

Eg3: So for Item 2 it'll be a total of 270.

Hope someone is able to shed some light on how these can be calculated.

Thank you!

1 ACCEPTED SOLUTION
Super User

Hi @msdf ,

Create a disconnect table with dates and another for the future week.

``````Item Value = SUM('Fact'[Value])

Date Diff =
DATEDIFF(
MIN(DAtes[Projected_Week_Date]),
MAX(DAtes[Projected_Week_Date]),
WEEK
)

Total Item Value =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff]
)

RETURN
SUMX(
temptable,
'Fact'[Value]
)

Total Item Value Future Week =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff] + SELECTEDVALUE(Future[Future_Week])
)

RETURN
SUMX(
temptable,
'Fact'[Value]
)
``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

2 REPLIES 2
Super User

Hi @msdf ,

Create a disconnect table with dates and another for the future week.

``````Item Value = SUM('Fact'[Value])

Date Diff =
DATEDIFF(
MIN(DAtes[Projected_Week_Date]),
MAX(DAtes[Projected_Week_Date]),
WEEK
)

Total Item Value =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff]
)

RETURN
SUMX(
temptable,
'Fact'[Value]
)

Total Item Value Future Week =
VAR temptable = FILTER(
'Fact',
'Fact'[Proj_Create_Date] = MIN(DAtes[Projected_Week_Date]) && 'Fact'[Projected_Week] <= [Date Diff] + SELECTEDVALUE(Future[Future_Week])
)

RETURN
SUMX(
temptable,
'Fact'[Value]
)
``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Hi @MFelix

Thank you so much for the above solution. I did have to change the fields around a bit on the calcs, but once I got my head around it, it worked like a charm. I also got to learn about disconnected tables. My new favourite way to use DAX.

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