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 there,
This might be quite a simple one - sorry that I am new to DAX and learning. I have the following table (simplified)
Asset | Date | Value |
aaa | 24/07/2020 | 20 |
aaa | ||
aaa | 25/07/2022 | 30 |
aaa | 01/04/2019 | 50 |
aaa | 04/06/2019 | 40 |
bbb | 17/03/2021 | 70 |
bbb | 10/02/2019 | 50 |
bbb | ||
bbb | 14/03/2020 | 40 |
bbb | 11/02/2018 | 80 |
bbb | 10/02/2017 | 90 |
ccc | 02/02/2019 | 30 |
ccc | 02/02/2018 | 30 |
I am trying to create a table in my report that will reference this table to make it look like this:
Asset | Current Value | Previous Value | % Difference |
aaa | 30 | 20 | |
bbb | 70 | 40 | |
ccc | 30 | 30 |
Basically I would like the asset listed 'distinctly', and then for current value column:
- each decided by finding the most recent date, and returning the value
For previous value
- find the second to last most recent date and return the value
For % difference
- The % difference between the above two
Any help really appreciated.
Solved! Go to Solution.
It seems I forgot to include the SUM() function here.
Try ->
VAR ValueSecondLastDate = Calculate(SUM(Table1[Value]), Table1[Date] = SecondLastDate)
When writing measures, you need to use functions on the columns
Edit to Above Formula ->
Try the following ->
ValueLastDate = Calculate(SUM(Table1[Value]), LASTDATE(Table1[Date]))
Value Delta % =
VAR Delta = ValueLastDate - ValueSecondLastDatee
Return
Calculate(Divide(Delta, ValueSecondLastDate))
Hi @arichard19
Many thanks for this! I have split into two measures. Measure 1:
ValueLastDate = Calculate(SUM(Table1[Value]), LASTDATE(Table1[Date]))
This works fine.
But measure 2:
It seems I forgot to include the SUM() function here.
Try ->
VAR ValueSecondLastDate = Calculate(SUM(Table1[Value]), Table1[Date] = SecondLastDate)
When writing measures, you need to use functions on the columns
Thank you so much @arichard19 for you help. Works perfectly. I just added the square brackets for the references to other measures where needed in the DAX
Try the following ->
LastDate = Calculate(SUM(Table1[Value]), LASTDATE(Table1[Date]))
SecondLastDate =
VAR LastDate = LASTDATE(Table1[Date]))
RETURN
Calculate(SUM(Table1[Value]), MAX(Table1[Date]) < LastDate))
Delta % =
VAR Delta = LastDate - SecondLastDate
Return
Calculate(Divide(Delta, SecondLastDate))
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 |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |