Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Solved! Go to Solution.
Hi,
Can you share a dataset and also show the expected result.
Hi, Below is the data and expected result, I have tried to put three measures - Close -1, Close -3 and Close-4 to make clear what I am expecting. Thanks!
Date | Symbol | Open | High | Low | Close |
11/3/2017 | ABC | 118.20 | 126.45 | 117.65 | 123.60 |
11/3/2017 | XYZ | 1,776.00 | 1,789.35 | 1,767.00 | 1,772.00 |
11/4/2017 | ABC | 143.20 | 151.45 | 142.65 | 148.60 |
11/4/2017 | XYZ | 1,801.00 | 1,814.35 | 1,792.00 | 1,797.00 |
11/7/2017 | ABC | 149.75 | 152.30 | 141.20 | 147.95 |
11/7/2017 | XYZ | 1,809.85 | 1,816.15 | 1,776.35 | 1,781.00 |
11/8/2017 | XYZ | 1,798.90 | 1,808.35 | 1,775.30 | 1,776.00 |
11/8/2017 | ABC | 148.05 | 158.40 | 146.40 | 151.30 |
11/9/2017 | XYZ | 1,787.00 | 1,794.35 | 1,754.25 | 1,758.00 |
11/9/2017 | ABC | 151.85 | 155.90 | 149.10 | 155.40 |
11/10/2017 | ABC | 154.35 | 158.25 | 151.80 | 156.60 |
11/10/2017 | XYZ | 1,754.85 | 1,775.05 | 1,745.00 | 1,755.00 |
Result Expected: | |||||
Date | Symbol | Close -1 | Close -3 | Close - 4 | |
11/10/2017 | ABC | 155.40 | 147.95 | 148.60 | |
11/10/2017 | XYZ | 1,758.00 | 1,781.00 | 1,797.00 | |
11/9/2017 | ABC | 151.30 | 148.60 | 1,772.00 | |
11/9/2017 | XYZ | 1,776.00 | 1,797.00 | 123.60 |
Hi @hnsbhat
Please try this MEASURE
Please see the attached file here
Previous_25th_Price = VAR Noofrecords = COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[Dates] < SELECTEDVALUE ( Table1[Dates] ) ) ) RETURN CALCULATE ( VALUES ( Table1[Price] ), EXCEPT ( TOPN ( Noofrecords - 24, ALL ( Table1[Dates] ), Table1[Dates], ASC ), TOPN ( Noofrecords - 25, ALL ( Table1[Dates] ), Table1[Dates], ASC ) ) )
Hi @hnsbhat
Similarly you can get the previous 25th date using the MEASURE below
Previous_25th_Date = VAR Noofrecords = COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[Dates] < SELECTEDVALUE ( Table1[Dates] ) ) ) RETURN CALCULATE ( MAX ( Table1[Dates] ), EXCEPT ( TOPN ( Noofrecords - 24, ALL ( Table1[Dates] ), Table1[Dates], ASC ), TOPN ( Noofrecords - 25, ALL ( Table1[Dates] ), Table1[Dates], ASC ) ) )
Thanks for your reply, I tried it, but since I was trying it in excel 2016 powerpivot, it seems it doesn't have function SELECTEDVALUE. I searched for alternative and found this - IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ) ). But even with this I am not getting any values but only Blank.
Hi @hnsbhat
Try this Measure for Close 1
Close 1= VAR Noofrecords = IF ( HASONEVALUE ( Table1[Dates] ), COUNTROWS ( FILTER ( ALLEXCEPT ( Table1, Table1[Symbol] ), Table1[Dates] < VALUES ( Table1[Dates] ) ) ) ) RETURN CALCULATE ( MAX ( Table1[Close] ), EXCEPT ( TOPN ( Noofrecords - 1, ALLEXCEPT ( Table1, Table1[Symbol] ), Table1[Dates], ASC ), TOPN ( Noofrecords - 2, ALLEXCEPT ( Table1, Table1[Symbol] ), Table1[Dates], ASC ) ) )
Hi Zubair, Thank you, It works. However it only works when I try with the sample data I had provided, if I try with complete data nothing is coming again. I am giving the links for both sample and complete data file I have used below.
And with test data also I am not able to change the number of records to look back.
Test Data - https://drive.google.com/open?id=18F7wQ4IkDwv2IQ4Cc3-0vgNeMaDFYaaD
Complete data - https://drive.google.com/open?id=1VSTxwxI4Hphcyp7MOn4K4KCG2GRsHqc_
Thank you All for your help, I am able to get what I wanted by using the below approach.
1. Add a Index Column (Using Query Editor)
2. Create a calculated Column in powerpivot
Rank = VAR Symbol = Table1[Symbol] RETURN RANKX ( FILTER ( ALL ( Table1 ), Table1[Symbol] = Symbol ), Table1[Index], , ASC )
3. Create The PreviousRow Column Or in a Measure (Replace VAR Index = Table1[RANK]-1 with VAR Index=min(Table1[Rank])-1)
Close -1 = VAR Index = Table1[Rank] - 1 RETURN CALCULATE ( SUM ( Table1[Close] ), FILTER ( ALLEXCEPT ( Table1, Table1[Close] ), Table1[Rank] = Index ) )
Hi @hnsbhat
Good One
Close 1= VAR Noofrecords = IF ( HASONEVALUE ( Table1[Date] ), COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[Date] < VALUES ( Table1[Date] ) && Table1[Symbol 2] = VALUES ( Table1[Symbol 2] ) ) ) ) RETURN IF ( HASONEVALUE ( Table1[Date] ), CALCULATE ( MAX ( Table1[Close] ), EXCEPT ( TOPN ( Noofrecords, FILTER ( ALL ( Table1 ), Table1[Date] < VALUES ( Table1[Date] ) && Table1[Symbol 2] = VALUES ( Table1[Symbol 2] ) ), Table1[Date], ASC ), TOPN ( Noofrecords - 1, FILTER ( ALL ( Table1 ), Table1[Date] < VALUES ( Table1[Date] ) && Table1[Symbol 2] = VALUES ( Table1[Symbol 2] ) ), Table1[Date], ASC ) ) ) )
Yes that works as well. Thank you 🙂
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |