Financial Reporting with Power BI - Prior Year Values Errors when selecting 2018 in the income statement

DAX DAX Calculations
1

Hi Everyone,

I am currently having a look at the Financial Reporting with Power BI course with regards to the Income statements & Prior Year Values

Please find attached a pbix file

There are 2 tabs - a Matrix tab which shows rows relating to Revenue/Gross Profit & Net Profit for 4 years and an Income statement tab which has 2 columns. One for Current Year and the 2nd for Prior Year

Please see further information below:

  1. when I select 2018 in the Date Slicer, I see both columns for 2018 being the current year and 2017 being the prior year.
    However the numbers for 2017 are not accurate when I select the 2018 Year slicer eg Current Revenue in 2018 is $34,843 and Prior Year (2017) shows $37,872 (which is incorrect)

  2. when selecting 2017 in Date Slicer - the Revenue numbers for 2017 is $56,566 and 2016 is $56,929

  3. Similarly when selecting the Year 2016, Revenue numbers are $56,929 & $57,654 and its also correct for 2015

Question - when selecting 2018 using the date slicer, why is the prior year number for 2017 not accurate?

Many thanks

Financial Reporting In Power BI_practice.pbix (656.2 KB)

2

Hi @atin,

When you use DATEADD function, it gets only the period that exists on values.
See the difference between DATEADD and PARALLEL PERIOD:

Capture
Capture1351Ă—332 20.9 KB

2018 has data until August, if you sum the 2017 year until the same month, that’s it.

DATEADD function works like a dynamic filter context.
PARALLEL PERIOD is statical filter.

Ricardo

1 Like
3

Hi @ricardocamargos88

Many thanks for your comments.

Unfortunately your solution does not answer my question as I am following the " Financial Reporting with Power BI structured course on a Step by Step basis, using the videos created by Sam, the trainer.

The PARALLEL PERIOD function was not mentioned in the videos. He uses the DATEADD function to calculate his prior year numbers and upon review of his pbix files, the years are all correct irrespective of the years chosen.

I have followed all the steps in my file and unsure why the error exists. For further information, the course is one of the courses on the portal and my question relates to the Income Statement Section.

4

Hi @atin,

Maybe it wasn’t so clear.

I downloaded file “Financial Reporting In Power BI” from the course and compared with the file provided by you.

If you take a look on the date table, your file has date until 2018 August and the file from the course has dates until 2020 May.

Give it a shot filtering on Query Editor the date table for values until 2018 August. It will produce the same value as your file.

DATEADD is a dynamical function for dates, it will match the period (days). Once you just have 2018 August it will get from 2017 (January - August).

PARALLEL PERIOD is statical. It will get the parameter and filter for that period (in this case Years).So it gets all the values for 2017.

It is just a matter of how the engine handles the query.

If you want more details about it, there is a good post here:

I hope it is clear now.

1 Like
5

Hi @ricardocamargos88

Many thanks for your explanation. Yes you are correct - the problem was the date table on my file. I have now amended this and seeing the correct results now

I will also be taking a look at the post you sent including your comments on PARALLEL PERIOD and see how I can incorporate this in future reports. It would certainly be a value add function that I can use moving forward. Many thanks

Best wishes.

6

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!