Calculating sums based on multiple slicers (parameters)

[msdf]

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! 

[MFelix · Accepted solution]

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

Please see file attach.

[msdf]

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!