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.
Hello everyone,
I have the following raw data from the pricing sharepoint:
ID; Date on which the price was modified (updated), Start/End of the price period, the respective price), and a key (ID+Currency).
OriginalID | Modified | Rank | Index | StartDateP1 | EndDateP1 | Price End P1 | StartDateP2 | EndDateP2 | Price End P2 | Key |
1111 | 4.3.2021 | 4 | 1 | 1.1.2021 | 31.12.2021 | 9,19 | 1111EUR | |||
1111 | 29.21.2021 | 2 | 2 | 1.1.2021 | 31.12.2021 | 9,19 | 1.1.2022 | 31.12.2022 | 9,19 | 1111EUR |
1111 | 8.4.2022 | 3 | 3 | 1.1.2021 | 31.12.2021 | 9,19 | 1.1.2022 | 31.12.2022 | 9,17 | 1111EUR |
1111 | 12.4.2022 | 1 | 4 | 31.12.2020 | 30.12.2021 | 9,19 | 31.12.2022 | 9,17 | 1111EUR | |
2222 | 16.3.2022 | 4 | 5 | 31.12.2021 | 30.12.2022 | 50,01 | 31.12.2022 | 31.12.2023 | 52,71 | 2222EUR |
2222 | 23.3.2022 | 3 | 6 | 31.12.2021 | 30.12.2022 | 51,71 | 31.12.2022 | 31.12.2023 | 52,71 | 2222EUR |
2222 | 29.3.2022 | 2 | 7 | 31.12.2021 | 30.12.2022 | 51,71 | 31.12.2022 | 31.12.2023 | 52,71 | 2222EUR |
2222 | 18.4.2022 | 1 | 8 | 31.12.2021 | 30.12.2022 | 51,71 | 31.12.2022 | 31.12.2023 | 52,71 | 2222EUR |
I have created a formula that finds the 22' price from each price update:
The problem is that in some cases the first row in the 22' Price is Blank, because there was no 22' price in the beginning. It means that I need the first non blank price.
Do you have any ideas?
Thanks,
Leo
Solved! Go to Solution.
You need to apply FIRSTNONBLANK to the date. Then use that date as the filter to get the price at that date.
Also look into FIRSTNONBLANKVALUE - that might save you a step.
Or use the pedestrian way
22 Price =
var m = min('Table'[Modified])
return CALCULATE(sum('Table'[Price End P2]),'Table'[Modified]=m)
Hi @Anonymous
Please refer to sample file with the solution https://www.dropbox.com/t/0WkCrxsXlYxewGbb
22' Original Price =
VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[OriginalID] ) )
VAR NonBlankTable = FILTER ( CurrentIDTable, Data[22' Price] <> BLANK ( ) )
VAR FirstRecord = TOPN ( 1, NonBlankTable, Data[Index], ASC )
RETURN
MAXX ( FirstRecord, Data[22' Price] )
Thank you so much, @tamerj1
It works perfectly and it is exactly what I needed.
Hi @Anonymous
Please refer to sample file with the solution https://www.dropbox.com/t/0WkCrxsXlYxewGbb
22' Original Price =
VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[OriginalID] ) )
VAR NonBlankTable = FILTER ( CurrentIDTable, Data[22' Price] <> BLANK ( ) )
VAR FirstRecord = TOPN ( 1, NonBlankTable, Data[Index], ASC )
RETURN
MAXX ( FirstRecord, Data[22' Price] )
Thank you so much, @tamerj1
It works perfectly and it is exactly what I needed.
I would recommend you unpivot your data before proceeding. Or use FIRSTNONBLANK if you are in a hurry.
You need to apply FIRSTNONBLANK to the date. Then use that date as the filter to get the price at that date.
Also look into FIRSTNONBLANKVALUE - that might save you a step.
Or use the pedestrian way
22 Price =
var m = min('Table'[Modified])
return CALCULATE(sum('Table'[Price End P2]),'Table'[Modified]=m)
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |