Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Last vs previous value

Hi,   I need to compare two values of one measure: last value in the specific year (based on slicer) and the previous one (it can be the second last in a given year or the last one in the previous ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

According to my understand, you want to calculate the latest value of selected year in Slicer / the second latest value ,right?

 

Add a Year column and create a table with Year column for slicer,then you could use the following formula:

result =
VAR _sele =
    SELECTEDVALUE ( YearSlicer[Year] )
VAR theLatest =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            [Date]
                = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [Year] = _sele ) )
        )
    )
VAR theSecond =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [Date]
                = CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( ALL ( 'Table' ), [Date] < MAX ( 'Table'[Date] ) )
                )
        )
    )
RETURN
    DIVIDE ( theLatest, IF ( theSecond = BLANK (), 1, theSecond ) )

My final output looks like this:

Here is the pbix file.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

 

Best Regards,
Eyelyn Qin