Show value from previous row of table

[sjehanzeb]

I have reviewed solutions from previous related post - but they didn't work for me.

Expected Outcome

Table

Financial Year      Amount       Last Year Amount   Difference

2018-19               1000            900                          100

2017-18                900             700                          200

2016-17                700             400                          300

the report will be slice by a category named item type. it worked without slicer but I definitely need slicer. 

I have removed financial year relationship in the hope that this would work. calculated financial year through columns in main data report i..e itemized  report

below are some of the formulas for columns added for this purpose. Please note that i want to report data by financial year i.e. Jul-Jun

Financial StartDate = DATEADD('Itemized Report'[Date].[Date], -'Itemized Report'[Financial Period]+1,MONTH)-'Itemized Report'[Date].[Day]+1

iIndex = RIGHT('Itemized Report'[Financial StartDate].[Year],2)               // for creating an index

Financial Year = 'Itemized Report'[Financial StartDate].[Year] & "-" & RIGHT('Itemized Report'[Financial StartDate].[Year]+1,2)

Measure: (not working)

Last Year Amount =

var a = 'Itemized Report'[iIndex]-1 return

CALCULATE(SUM('Itemized Report'[Amount]), FILTER('Itemized Report', 'Itemized Report'[iIndex]=a))

Relationships

sample transactions

[Anonymous · Accepted solution]

Hi @sjehanzeb,

You can try to use the following measure if it meets for your requirement:

Measure =
VAR currDate =
    MAX ( Table[Date] )
VAR currYear =
    SELECTEDVALUE ( Table[Financial Year] )
VAR _prev =
    CALCULATE (
        MAX ( Table[Financial Year] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date] < currDate
                && [Financial Year] <> currYear
        )
    )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Financial Year] = _prev )
    )

If above not help, please share some dummy data to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[littlemojopuppy]

You are overcomplicating this.

Create a date table with your calendar year/month/date and fiscal year/month/date fields.  Use dates in the other tables and create relationships between them.

THEN...calculating last year's amount is as easy as CALCULATE([Total Value], PREVIOUSYEAR(Date[Date]))

[sjehanzeb]

can you show some example. i did try that earlier, I could not get it to work.