Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |