Balance Sheet - Comparison Prior Year

Hi all, first of all i’m a beginner user of power BI, i’m studing beginner course and now i’m following the webinar related the Financial Report.

Monthly, i prepare for CEO a financial report and i’m trying to convert my excel report into a Power Bi dashboard report.

I have a question related balance sheet, particularly it’s clear to obtain the actual value but i’m not able to compare the data with previous year, i explain better in my report i compare the current month with final data of previous year (for example oct 2020 compare with december 2019, nov 2020 with dec 2019, etc…), how i can solve that problem, it’s possible?

it’s clear in the income statement case, infact i’m able to compare oct 2020 with october 2019, etc… with daxformula samelastperiod, but i can’t solve the problem in balance sheet where i need to compare with the final data of previous year.

I need only some suggestion, at moment i’haven’t build my report but i’m trying to build the project in my mind.

thanks very much

Antonio

Hello @antonio81m,

Thank You for posting your query onto the Forum.

Well I sense that you want to analyze the current year data with the previous year where previous years data will be static inspite of selections made into the slicers pertaining to the months. That is, you want to compare current year or months data with the end month of the previous financial year.

Just couple of days back I provided the solution based on this sort of requirement. I’m providing a link below for the reference of the solution provided on that post from where you can also download the working of my PBIX files for the better understanding of the concept.

Also without looking at the data or working of your file it will be very much difficult for the forum members to judge and provide the results efficiently and in a better manner.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note: Please click onto the link in order to view the entire post and not onto the expand/collapse button.

Thanks and Warm Regards,
Harsh

Thank you very much for your quickly response. So that it’ very difficult to help me without my file. In the next days i will begin to work on it. I need before to close FY in the old excel format for my ceo and than i will work on my project so i will ask you and other members some suggestments.

I will see your post and i will give you a feedback

Thanks

Antonio

Hi @antonio81m , did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Report BI 31.12.2020.pbix (401.5 KB)

Dear Harshe

i have tryed to solve the problem but without success. I have prepared a light version of the report to clear you my problem (sorry it’s in italian language):

  1. I follow instruction of webinar Financial Reporting;
  2. for the Income Statement for me it’s clear how to compare for example oct 2020 vs oct 2019, it is also explained by webinar (sheet Conto Economico)
  3. for balance sheet (stato patrimoniale), i’m not able to solve the problem, i want to compare october 2020 with dec 2019 (or gen 2021 vs dec 2020, etc.)

thank you very much for your help, I try to follow your tread without success.

regards

Antonio

Hello @antonio81m,

Thank You for providing the working of the PBIX file.

But my sincere apologies, I’m not good/proficient with the languages other than English and due to this I’m finding it difficult to understand what’s what. I hope someone from our forum who understands the Italian language can help you out.

Again my sincerest apologies to you.:pray:

Harsh

Hi @antonio81m

Looking at your requirement, I have come up with below solution. Let me know if it’s helpful or any other help requrired.

  1. Relation b/w Calendar and “Conto Economico” was on Month Year. For Time Related Calculations always create relationship b/w Date Columns else you may not get proper result. I created below Date Column inside “Conto Economico” table and created 1*Many relationship with date table.

Date = Date(right('Conto Economico'[Mese/Anno],4),switch(left('Conto Economico'[Mese/Anno],3),"Gen",01,"Dic",12,"Nov",11,"Ott",10,"Sett",09,"Ago",08,"Lug",07,"Giu",06,"Mag",05,"Apr",04,"Mar",03,"Feb",02),01)

  1. Created below measure to get Last Year Dec value. It will extract Month-number of current date and subtract with Current date to get Dec of Last Year.

     CE Importo LY Dec =
     VAR monthCD =
         MONTH ( MAX ( Calendario[Date] ) )
     RETURN
         CALCULATE ( [CE Importo], DATEADD ( Calendario[Date], - monthCD, MONTH ) )
    

Please find solution attached. EDNA_Solution_Report BI 31.12.2020.pbix (408.5 KB)

Thanks
Ankit J

Hi Ankit, thank you very much for your help, you’re great, you solve my problem :slight_smile:

Thanks very much for your help

Regards

Antonio

1 Like