Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |