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.
Hi,
I'm struggeling to get the previous value based on the last known price.
My goal is to calculate " this months" volume with last known price before that specific month.
I've tried several options as lastnonblank etc but nothing seems to work yet.
" last value" in July needs to be 1.02 as shown below
Solved! Go to Solution.
Hi,
You may download my PBI file from here. See the last field dragged to the measure. For March, you will see the value as 0.5720 (the last known weighted average price of the previous year).
Hope this helps.
Hi,
Share the link from where i can download your PBI file.
@Ashish_Mathur thanks for reaching out, find the link below.
PrevAmount works for the current year but still have issues with last known value from previous year.
Hi,
You may download my PBI file from here. See the last field dragged to the measure. For March, you will see the value as 0.5720 (the last known weighted average price of the previous year).
Hope this helps.
Hi, @Ashish_Mathur
Works great, Thanks!
I'm also looking for the EUR/KG value from the same month as the last know value, I tried the same formula but it returns blanks.. do you have a solution for this as well?
You are welcome. If my previous reply helped, please mark it as Answer. Download the PBI file from here with the solution of your new request
@RK91 You are going to need some kind of date or numeric month number field. Worst possible case, create a column like this:
Month Number = SWITCH([Month],
"January",1,
"February",2,
"March",3,
"April",4,
...
)
Then you can do this:
Last Value Column =
VAR __Table = FILTER(ALL('Table'),NOT(ISBLANK('Table'[last value])) && [Month Number] < EARLIER([Month Number])
VAR __LastMonthKnown = MAXX(__Table,[Month Number])
RETURN
MAXX(__Table,[Month Number] = __LastMonthKnown),[last value])
Last Value Measure =
VAR __CurrentMonth = MAX('Table'[Month Number])
VAR __Table = FILTER(ALL('Table'),NOT(ISBLANK('Table'[last value])) && [Month Number] < _CurrentMonth
VAR __LastMonthKnown = MAXX(__Table,[Month Number])
RETURN
MAXX(__Table,[Month Number] = __LastMonthKnown),[last value])
Thanks for reaching out, I got there using:
@RK91 Where did you place your ALL?
@Greg_Deckler I tried replacing allselected and wrapped the complete measure in one.. I know not how it should be, but kinda lost in this one
Try this:
CALCULATE(LASTNONBLANK(ALL(aterial[MM-YY])
If not post sample data so that we can recreate.
@Greg_Deckler didn't work, see the sample data below
MM-YY Material Category EUR/UNIT Volume
01/12/2019 A material A category € 1,040 50.000
01/01/2020 A material A category
01/02/2020 A material A category
01/03/2020 A material A category € 1,023 50.000
01/04/2020 A material A category
01/05/2020 A material A category
01/06/2020 A material A category
01/07/2020 A material A category € 1,006 100.000
01/08/2020 A material A category
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |