Forum Discussion
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!
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.
2 Replies
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.
