Measure that determines if Costs go up, same, or down from Period to Period

[Anonymous]

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??

[v-yingjl]

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.

[richbenmintz · Accepted 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

sample.pbix 

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!

[Anonymous]

Wow..that's some advanced DAX!

[v-yingjl]

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

[Greg_Deckler]

@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.

[Anonymous]

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.

[richbenmintz]

Hi @Anonymous,

Can you provide some sample data?

[parry2k]

@Anonymous it will be easier if you paste some sample data and expected output.