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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
msdf
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]

msdf_0-1722567305176.png

 

 

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

 

msdf_0-1722564591785.png

 

 

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
MFelix
Super User
Super User

Hi @msdf ,

 

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

 

Now add the following measures:

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

 

MFelix_0-1722612400038.png



Please see file attach.

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @msdf ,

 

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

 

Now add the following measures:

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

 

MFelix_0-1722612400038.png



Please see file attach.

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



msdf
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. 

Thanks for your help! 

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.