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.
I have a table that has a column called "Cost", which contains the amount spent on Cloud Consumption for a given Period. My Periods are not assigned to a date object right now, however, they are indexed. So each row of the table has the team name and the period "P1_2020", and the cost for that period. I'd like to write a measure that determines if the cost from one period is higher, equal, or lower than the cost from the previous period. Not sure how to approach this. Am I going to need my periods assigned to DATE objects??
Solved! Go to Solution.
Hi @Anonymous,
Based your requirement I mocked up some data and derived a date column based on the pattern of the index field
then created a measure that gets the prior value and measure that displays up/down/sideways depending on the trend
Prior Value =
var pDate = DATEADD('Table'[Index Date], -1, MONTH)
var pYear = CALCULATE(max('Table'[Year]))
return
SWITCH(
TRUE(),
HASONEVALUE('Table'[Index Date]),
CALCULATE(sum('Table'[Value]), FILTER(ALLEXCEPT('Table', 'Table'[Team]), 'Table'[Index Date] = pDate)),
HASONEVALUE('Table'[Year]),
CALCULATE(sum('Table'[Value]), FILTER(ALLEXCEPT('Table', 'Table'[Team]), 'Table'[Year] = pYear -1)),
Blank()
)
Trend =
VAR down = UNICHAR(11167)
VAR up = UNICHAR(11165)
VAR same = UNICHAR(11166)
var cur = sum('Table'[Value])
Return
SWITCH(True(),
[Prior Value] = BLANK(), BLANK(),
cur < [Prior Value], down,
cur > [Prior Value], up,
cur = [Prior Value], same
)
Credit to Matt Allington, https://exceleratorbi.com.au/dax-unichar-function-power-bi/, for the assist with the UNICHAR values for up/down and sideways
Results as Matrix below
Link to pbix file below
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @Anonymous ,
If you have already had an index column, you need not assign date objects. Measures would be like this:
Previous value =
VAR _index =
SELECTEDVALUE ( 'Table'[Index] )
RETURN
CALCULATE (
MAX ( 'Table'[cost] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index]
= CALCULATE (
MAX ( 'Table'[Index] ),
FILTER ( ALL ( 'Table' ), 'Table'[Index] < _index )
)
)
)
Comparsion =
SWITCH (
TRUE (),
'Table'[Previous value] = BLANK (), "No data in previous",
SELECTEDVALUE ( 'Table'[cost] ) > 'Table'[Previous value], "UP",
SELECTEDVALUE ( 'Table'[cost] ) = 'Table'[Previous value], "Equal",
SELECTEDVALUE ( 'Table'[cost] ) < 'Table'[Previous value], "Down"
)
Attached sample file that hopes to help you that you can refer: compare values.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous,
Based your requirement I mocked up some data and derived a date column based on the pattern of the index field
then created a measure that gets the prior value and measure that displays up/down/sideways depending on the trend
Prior Value =
var pDate = DATEADD('Table'[Index Date], -1, MONTH)
var pYear = CALCULATE(max('Table'[Year]))
return
SWITCH(
TRUE(),
HASONEVALUE('Table'[Index Date]),
CALCULATE(sum('Table'[Value]), FILTER(ALLEXCEPT('Table', 'Table'[Team]), 'Table'[Index Date] = pDate)),
HASONEVALUE('Table'[Year]),
CALCULATE(sum('Table'[Value]), FILTER(ALLEXCEPT('Table', 'Table'[Team]), 'Table'[Year] = pYear -1)),
Blank()
)
Trend =
VAR down = UNICHAR(11167)
VAR up = UNICHAR(11165)
VAR same = UNICHAR(11166)
var cur = sum('Table'[Value])
Return
SWITCH(True(),
[Prior Value] = BLANK(), BLANK(),
cur < [Prior Value], down,
cur > [Prior Value], up,
cur = [Prior Value], same
)
Credit to Matt Allington, https://exceleratorbi.com.au/dax-unichar-function-power-bi/, for the assist with the UNICHAR values for up/down and sideways
Results as Matrix below
Link to pbix file below
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Wow..that's some advanced DAX!
Hi @Anonymous ,
If this issue is solved, please kindly mark the above posts which is helpful as a solution to help others find it more quickly. Thanks!
Best Regards,
Yingjie Li
@Anonymous That would be easiest probably. However, you could use something like:
Previous Cost (Column) =
__PreviousIndex = MAXX(FILTER('Table',[Team]=EARLIER('Table'[Team])),[Index])
RETURN
MAXX(FILTER('Table',[Index] = __PreviousIndex),[Cost])
Basically, it is the Lookup Min/Max pattern. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Since you are new, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Thanks for you help. I was able to take a different route that ended up working. Had each fiscal period as a separate column so I could just compare within the same row.
Hi @Anonymous,
Can you provide some sample data?
Proud to be a Super User!
@Anonymous it will be easier if you paste some sample data and expected output.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |