cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wilm117
Helper I
Helper I

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

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.

Wilm117_0-1625502103866.png

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: 

Wilm117_1-1625502189571.pngWilm117_2-1625502218748.png

 

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!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Wilm117
Helper I
Helper I

Thanks, this worked!

amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!