Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have column X ,column Y and Date column. I want
X/Y = Spread
(X/Y)/(First date of (X/Y))-1="A"
(X/Y)/(First date of (X/Y))-1-(Sum of Previous "A")
I am attaching my data table. Kindly help.
Thanks
Date | X | Y |
12/01/2021 | 98 | 170 |
28/02/2021 | 78 | 189 |
22/11/2021 | 65 | 123 |
04/11/2021 | 23 | 143 |
Solved! Go to Solution.
Hi @Hamdan1234 ,
Please try:
Spread = [X]/[Y]
A =
var _firstdate=CALCULATE( FIRSTDATE('Table'[Date]),ALL('Table'))
return [Spread] / CALCULATE(MAX('Table'[Spread]),FILTER('Table',[Date]=_firstdate)) -1
B =
var _sumofpre=CALCULATE(SUM('Table'[A]),FILTER('Table',[Date]<=EARLIER('Table'[Date])))
return [A] -_sumofpre
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hamdan1234 ,
Please try:
Spread = [X]/[Y]
A =
var _firstdate=CALCULATE( FIRSTDATE('Table'[Date]),ALL('Table'))
return [Spread] / CALCULATE(MAX('Table'[Spread]),FILTER('Table',[Date]=_firstdate)) -1
B =
var _sumofpre=CALCULATE(SUM('Table'[A]),FILTER('Table',[Date]<=EARLIER('Table'[Date])))
return [A] -_sumofpre
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Hamdan1234 ,Can you share sample output in table format?
User | Count |
---|---|
106 | |
86 | |
81 | |
73 | |
71 |
User | Count |
---|---|
112 | |
101 | |
96 | |
74 | |
67 |