Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a monthly sales dataset, like this:
MonthIndex | Sales |
1 | 45 |
2 | 55 |
3 | 45 |
4 | 40 |
5 | 50 |
6 | 40 |
7 | 70 |
8 | 60 |
9 | 50 |
10 | 55 |
11 | 50 |
12 | 40 |
And I have a table of factors, like this:
MonthsFromCurrent | Factor |
0 | .7 |
1 | .5 |
2 | .2 |
3 | .06 |
I need a measure that, for the given month, calculates the sum of the products of the sales and factors. For example, if the current month is 6, I want the measure to output (40*.7 + 70*.5 + 60*.2 + 50*.06) = 78
The factor for all other months is 0.
Solved! Go to Solution.
Hi @Brotedo ,
Maybe you can try this.
Measure =
VAR _currentmonth =
SELECTEDVALUE ( Parameter[Parameter] )
VAR _f =
ADDCOLUMNS (
'factors',
"sales1",
CALCULATE (
SUM ( sales[Sales] ),
FILTER (
'sales',
[MonthIndex]
= EARLIER ( [MonthsFromCurrent] ) + _currentmonth
)
) * [Factor]
)
RETURN
SUMX ( _f, [sales1] )
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Brotedo ,
Maybe you can try this.
Measure =
VAR _currentmonth =
SELECTEDVALUE ( Parameter[Parameter] )
VAR _f =
ADDCOLUMNS (
'factors',
"sales1",
CALCULATE (
SUM ( sales[Sales] ),
FILTER (
'sales',
[MonthIndex]
= EARLIER ( [MonthsFromCurrent] ) + _currentmonth
)
) * [Factor]
)
RETURN
SUMX ( _f, [sales1] )
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Brotedo
please try
NewMeasure =
SUMX (
FILTER (
ALLSELECTED ( Sales ),
Sales[MonthIndex] >= MAX ( Sales[MonthIndex] )
&& Sales[MonthIndex]
< MAX ( Sales[MonthIndex] ) + COUNTROWS ( Factors )
),
SUMX (
FILTER ( Factors, Factors[MonthsFromCurrent] = Sales[MonthIndex] ),
Sales[Sales] * Factors[Factor]
)
)
User | Count |
---|---|
53 | |
35 | |
20 | |
16 | |
15 |
User | Count |
---|---|
94 | |
72 | |
30 | |
22 | |
14 |