Forum Discussion
Solved
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_...
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.
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.
