Compare report with data from previous period, not previous month,quarter etc

[Wilm117]

Hi everybody,

I have a question that pop up in almost all my report and where i fail to get a good grip on. 

I work in projects that periodically gather financial report that have to be analysed and used to govern these projects. The question that always comes back is that the project managers always want to see a comparison with the previous report and what the differences in financial performance are. each department has 3 moments a year in which they have to hand in their reports. I have connected a folder where all these reports are saved, so that they will be listed in Power BI.

The column VGR indicates the period of the financial report handed in by a department. (VGR1-2021 = the first financial report of 2021 and there are 3 in total),

These reports have seperate underlying pages that are connected to other fields: 

So, i want to be able to make a comparison between the most recent financial reports (indicated by VGR1-2021, VGR2-2021 etc) and the previous financial report. I want to build this in a way that this data automatically changes when new data is uploaded. So if VGR3-2021 is added to the data, i automatically want to generate the comparison between VGR2-2021 and VGR3-2021 instead of VGR2-2021 and VGR1-2021.

I already tried many of the DAX formulas like, DATEADD, MTD, PREVIOUSDATE, PREVIOUSPERIOD, but I (or they) fail to generate the right results and i feel it is because most of the posts are more aimed at comparing with the previous year, months or quarter. Since my data is slightly different i fail to get the right formula to establish this. In a previous post i asked a similar questions and got this advice, which worked. However i can't get it to function in my current report. 

2nd Latest = 
VAR latestdate =
    CALCULATE ( MAX ( 'NPO dump'[index] ), ALL ( 'NPO dump' ) )
VAR secondlatestdate =
    CALCULATE (
        MAX ( 'NPO dump'[index] ),
        FILTER ( ALL ( 'NPO dump' ), 'NPO dump'[index] < latestdate )
    )
VAR currentniv =
    MAX ( 'NPO dump'[NPO uniek] )
VAR currentpost = MAX('NPO dump'[Post])
VAR result =
    COALESCE (
        CALCULATE (
            SUM ( 'NPO dump'[Bedrag] ),
            KEEPFILTERS (
                FILTER (
                    ALL ( 'NPO dump' ),
                    'NPO dump'[Post] = "ATB"
                        && 'NPO dump'[index] = secondlatestdate
                        && 'NPO dump'[NPO uniek] = currentniv
                )
            )
        ),
        BLANK()
    )
RETURN
    result

Does anyone have any advice on how to fix this and how to build my data model in such a fashion that i can use (pretty much) the same formula in other reports? 

Thanks you!

[Wilm117]

Thanks, this worked!

[amitchandak · Accepted solution]

@Wilm117 , You need create a separate table with these value VGR2-2021  (say date)

Move year before in a new column

FY Report period =  right([FY report],4) & "-" & left([FY report],4) 

Not you can create a Rank column

Period Rank = RANKX(all('Period'),'Period'[FY Report period ],,ASC,Dense)

Then create measures like
This Period = CALCULATE(sum('Table'[Value]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('Table'[Value]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))