Last vs previous value

[Anonymous]

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 year if there is only one appeance in the whole year). In other words, I have to show the last value of the year and how it has changed in relation to the previous occurrence. Any ideas how to get these two values? 

Kind regards

[v-eqin-msft]

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

[amitchandak]

@Anonymous , Not very clear, but what you can try with time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below




This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.