The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
working on a Power Bi matrix style report, like below.
Slicer to select two Dates (years,qts) to compare prices per product category for.
How do I write a measure to calculate deltas of the two price categories (low, high) related to the selected dates. So that, the formula dynamically reference the Dates selection to filter respective product prices.
Can somebody help, please?
so assuming the Date column has no relationship with a date dimension, I think it should be something like this:
delta price1 =
VAR d1 = MIN('atm'[Date])
VAR d2 = MAX('atm'[Date])
RETURN CALCULATE(MIN('atm'[Price1],'atm'[Date]=d2) - CALCULATE(MIN('atm'[Price1],'atm'[Date]=d1)
Thank you for your reply!
A bit confused about the parenthesis in the formula. I've tried to add some, as well.
Either way I'm only getting this error.
"A single value for colum 'Price1' in table 'Current' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result".
I actually don't need a single value it should be a column with values for every row (Product).
you tried someything like this?
delta price1 =
VAR d1 = MIN('atm'[Date])
VAR d2 = MAX('atm'[Date])
RETURN CALCULATE(MIN('atm'[Price1]),'atm'[Date]=d2) - CALCULATE(MIN('atm'[Price1]),'atm'[Date]=d1)
Hi, thanks for the reply.
I've adjusted the formula and added the measure to the Values pane with Prices. So, the Delta Measure calculation Columns have no errors, but probably don't reflect the Dates Slicer. I would need the delta columns at the end of the table to calculat dPrice and dPrice2 for 2023 vs. 2020. The dates to be selected dynamically. Not sure if this can work with Matrix type of report.
Appreciate if you can have one more look at it. Cheers.
It looks like you're also using the year of that date as a column in that grid. That means that the measure is in the context of that column. So you will only see deltas within one year.
So I suggest to get rid of that column. You could add measures to display the price at the start of the selection and the price at the end of the selection.
@sjoerdvnthanks for the feeedback. Using just one table atm. There are 3 slicers configured. Date, Tower, Country.
I need to calculate deltas between Price1 and Price2 for each product and two selectable dates at a time.
Date | Tower | Country | Category | Product | Price1 Low | Price1 High | Price 2 Low | Price 2 High |
2019-11-30 | Security | USA | Network | Product 1 | 85.6 | 95.7 | 252.6 | 282.5 |
2019-11-30 | Security | USA | Network | Product 2 | 363.9 | 396.7 | 1074.0 | 1170.8 |
2019-11-30 | Security | USA | Network | Product 3 | 268.1 | 292.8 | 791.3 | 864.2 |
2019-11-30 | Security | USA | Network | Product 4 | 3.6 | 4.0 | 10.6 | 11.8 |
2019-11-30 | Security | USA | Application | Product 5 | 4.1 | 4.6 | 12.1 | 13.6 |
2019-11-30 | Security | USA | Application | Product 6 | 1.2 | 1.3 | 3.5 | 3.8 |
2019-11-30 | Security | USA | Application | Product 7 | 231.0 | 257.2 | 681.8 | 759.1 |
2019-11-30 | Security | USA | Application | Product 8 | 269.1 | 298.3 | 794.2 | 880.4 |
2020-03-31 | Security | USA | Network | Product 1 | 85.6 | 95.7 | 252.6 | 282.5 |
2020-03-31 | Security | USA | Network | Product 2 | 363.9 | 396.7 | 1074.0 | 1170.8 |
2020-03-31 | Security | USA | Network | Product 3 | 268.1 | 292.8 | 791.3 | 864.2 |
2020-03-31 | Security | USA | Network | Product 4 | 3.6 | 4.0 | 10.6 | 11.8 |
2020-03-31 | Security | USA | Application | Product 5 | 4.1 | 4.6 | 12.1 | 13.6 |
2020-03-31 | Security | USA | Application | Product 6 | 1.2 | 1.3 | 3.5 | 3.8 |
2020-03-31 | Security | USA | Application | Product 7 | 231.0 | 257.2 | 681.8 | 759.1 |
2020-03-31 | Security | USA | Application | Product 8 | 269.1 | 298.3 | 794.2 | 880.4 |
Does not sound too hard, but you really need to include some samples of the tables you are using, and their relationships.
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
21 | |
14 | |
14 | |
9 | |
7 |