Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I'm trying to have a measure that results in the cumulative rate up to the specific year, considering the same months of prior years, as shown below.
I found a solution like this but it works for years and don't know how to build this for same month periods
=EXP(
SUMX(FILTER(ALL(Rates), Rates[Year]<=MAX(Rates[Year])),
LN(1 + Rates[Rate])))
I'd appreciate if anyone can help me on this.
Thanks!
Alex
Solved! Go to Solution.
Hi @AlexJim ,
Please try this measure:
Measure =
VAR _m =
MAX ( 'Table'[Date] )
VAR _f =
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( [Date] ) = YEAR ( _m )
&& MONTH ( [Date] ) = MONTH ( _m )
&& [Date] <= _m
&& [Product] = MAX ( 'Table'[Product] )
)
RETURN
PRODUCTX ( _f, [Rate] + 1 )
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 @AlexJim
Please refer to the attached sample file with the solution
Comulative Rate =
VAR CurrentDate =
MAX ( Rates[Date] )
VAR CurrentMonthTable =
FILTER ( ALLSELECTED ( Rates ), MONTH ( Rates[Date] ) = MONTH ( CurrentDate ) )
VAR TableOnAndBefore =
FILTER ( CurrentMonthTable, Rates[Date] <= CurrentDate )
RETURN
PRODUCTX ( TableOnAndBefore, 1 + Rates[Rate] )
Very helpful, thanks. I realise I need to split by product as well, but does not seem to filter properly. Sorry for my low level of DAX knowledge.
Thanks
Alex
Hi @AlexJim ,
Please try this measure:
Measure =
VAR _m =
MAX ( 'Table'[Date] )
VAR _f =
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( [Date] ) = YEAR ( _m )
&& MONTH ( [Date] ) = MONTH ( _m )
&& [Date] <= _m
&& [Product] = MAX ( 'Table'[Product] )
)
RETURN
PRODUCTX ( _f, [Rate] + 1 )
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.
User | Count |
---|---|
56 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |