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

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

  • 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