Just what I needed. New to PowerBi and Im tasked to full-stack financial statements at my employment. Funny your video dropped 24 hours after my assignment. Perfect timing!

Bas - I don't know what sort of mind reading ESP super power you have but every time you put out a new video it solves or addresses exactly what I needed at that time! We are starting to convert all of our financial statements from Excel to Power Bi and this video is EXACTLY what I needed. And as always, so incredibly well explained. Thank you!

This is brilliant. I like the workarounds you display here (e.g. sorting with blanks). A lot of steps. I have watched this video multiple times.

This is great! I've created financial reports in Power BI before but I'd not created the different levels for dilldown so your guide was an ey-opener for me. The only problem I had with one financial report was when I created a seperate measure for each line item. The report used up all the memory and showed an error message when I uploaded it to the power bi service. In the end I created a single measure for the entire column that I needed to show, there were 10 columns in total. Your tutorial has now given me some more ideas for future reports. Thank you!

I’ve been looking for this but explained for dummies!! Thanks!!

Wow this is amazing. You're always very precise & meticulous when making you're reports. Makes me change my whole thinking on how to make a report. Very advanced content as well. Seems like you always have a solution for every problem. Well, well done! 👏🏽👏🏽👏🏽👍🏽

@HowtoPowerBI

2 жыл бұрын

Thanks a lot Ro. I love to hear it helped :) Sorry to disappoint though- there are problems i dont have a solution to ;) but still searching…

My job wants me to train on Power BI, and this is the first video I've ever understood. Thank you.

You are amazing man. a simple and to the point way of explaining things. Keep doing your magic.

Amazing video! Definitely going to try this on my next finance report!

Fantastic, just what I needed!

Astounding use of dax and native visual matrix!! Ty so much fort this Bas =).

Thank you soooooooo much! I’ve learnt so much from your videos! It’s very useful in my work! Thanks for sharing! You’re great!!!!!!!!!

@Bas: This is a SUPER awesome video. I REALLY LIKE those tips and tricks which make the financial folks (BTW-I'm one of them) really happy when seeing it. Thank you so much for sharing your knowledges! And you have the magic power to explain it in a way that non-technical folks can also understand.

@HowtoPowerBI

2 жыл бұрын

Glad it was helpful! :) thanks for the kind words

Great tutorial! Thank you!!!

Thank you! Thank you! Thank you! I've worked so hard on data modeling and I've been stuck on the presentation of the data in the visual. Tremendously helpful. There is no way I could have figured this out on my own.

@HowtoPowerBI

2 жыл бұрын

Glad it helped Melissa :):)

Thanks! Such a lifesaver! I constructed a P&L for work using Power Query in Excel on Essbase exports. I had upwards of 300 measures! Then, I decided to migrate it to Power BI. However, I couldn't re-create the Excel structure that was so easy in Excel; using calculated measures and pivots. Until I came across your How To video! I had already created the Line Item dim so I was on the right track. But, it was your "Financial Value" measure that helped link it all together for me. Thank you again! Subscribed and liked! 😃😃

@HowtoPowerBI

Жыл бұрын

Super happy to hear it helped :) Thank you for the support!

This workaround for sorting the line description by line number is simply ingenious!! Nice job!

@HowtoPowerBI

2 жыл бұрын

haha thx a lot- amazed myself that it works - but at the same time confused 😂

mate, you are awesome. really. i think you found your true calling; teaching. kuddos for this amazing piece of information, super useful and the brilliant engagement technique you've developed. cheers from brazil!

Awesome video, as usually I like your speed 😄 And the shortcut for edititng all the rows in query editor together was brilliant. Thanks a lot

Your are the best man!!!

As always awesome use of tricks and tips!!

This video is amazing. Game changer.

Amazing solution! I've just recently done something very similar for a client PnL. Instead I utilised Calculation Groups (which I learnt from your videos btw!) and a dim_Lookup to get the same effect. My Calculations Items were things like Sales, Cost of Goods Sold, Gross Margin and so on, then in a matrix I could apply each measure in turn with format string expressions e.g. Actuals, Budget, VAR, % etc.. I used the lookup then to drilldown into the various row items. With a bit of work you can also get decent formatting, but your option is a bit more format friendly. didn't catch the whole video so not sure if calculation groups were mentioned. Your method is an excellent way as well for those that might not have access to tabular editor.

This video just saved my life and I surprised my collogues. Thanks a ton Bas. You are undoubtly the best.

@HowtoPowerBI

2 жыл бұрын

that is amazing! awesome 😎👊

I’m Data Analist this Chanel is in my Top 5 Business Intelligence, greetings for 🇲🇽

Thank you very much for this video.

So brilliant with the blank rows 😊

Thank you so much Bas! :)

Great job!

You're simply the best 👌🏿 👍🏿. Thanks for sharing this knowledge. Microsoft power bi team should hire you.

Really great presentation

That's EXACTLY what I did 6 years ago... very good.

I will try this on weekend for sure! I was so distress with excel on big data using😊

You are amazing buddy!

This is genius! I was wondering how I could do this with my Business Central data. Great explanation; thank you so much.

It's really really great, it would be awesome if we can also have the budget comparison as well

This is a fantastic starting point for a lot of finance related reports I've been running lately and a great mindset for building other reports. Thanks for the fantastic video! I'll definitely be saving it for future reference!

@victormadoya1354

2 жыл бұрын

I have tried following the steps but my values are the same for each line. I have followed your steps but still unable to crack . Pls assist

@anuraja99

2 жыл бұрын

@@victormadoya1354 Typically the issue of same value on all rows is because Power BI cannot determine the relationship between tables - check the fact table to dimension table relationship again

Wooo woo woo.. what an awesome video! One of the rarest situation where we get what we wanted!

@HowtoPowerBI

2 жыл бұрын

Glad to hear this!

Amazing!! Thanks for the video, any future video on BALANCE SHEET ?

You are the Best. I am your Fan thanks for sharing your Knowladge with us.

@HowtoPowerBI

2 жыл бұрын

Ah Nuno, thank you!! Much appreciated:):)

Started to do similar thing about a month ago - and it took a lot of time and efforts, and although i managed to complete it it was really complicated. But in your video there is almost all needed information presented (except that we don't see tables and connections between which may be hard for novices) in short and easy to understand form, and it's really should be useful for many people. I guess now i should return to my report and try to remake it using some of your advices (especially a thing with ISINSCOPE).

@HowtoPowerBI

Жыл бұрын

Thanks for the feedback and all the best to your report :) hope it worked out!

This is awesome 👍👍

This is another level financial statement! thanks for the video.

@HowtoPowerBI

Жыл бұрын

Thanks for watching!

Fantasticcccccccccccc!!!!!!!!!

Thank you Bas

Thanks!

Hi You are the expert, but to complicated for normal users compare using excel with cube function which more flexible and easy understanding. But I will definitely try replicate the step you show us. Tqwm

Great video

I couldn't follow most of this. You are clearly at a level in Power BI that I am not even close to. I wish I had more experience/knowledge in this program. Just need to keep working at it.

Great video Bas, definety something to add in with a change innnthe columns to show currddnt monthband yearvtondate instead. Thanks for the help.

Thank you Very much, this is a grate!

@HowtoPowerBI

Жыл бұрын

Glad you think so!

Brilliant:)

Amazing i will try to add the accounts based on the same Logik

Thanks for this amazing video. Well explained. would you kindly advice how can the difference of [Financial Value] - [Financial Value - Previous Period] be calculated? Thank you.

Great video, it has helped me a lot! I have been scrolling through all comments but surprisingly it seems nobody else jumped into the issue I am facing: min at 14:10 of the video, it is suggested to include an IF statement to avoid an issue when sales = 0. But how about cases where sales is 0 but for example operating expenses are not 0 and we still want to show the P&L for a specific month, entity, country, product or whatever. Would be great to hear if others have had this issue, and what the correction to the DAX for "Financial Value" is or if any other setting needs to be adjusted to avoid this issue. Thanks!

thank you Bas !!

@HowtoPowerBI

2 жыл бұрын

thx for watching 😀😎

Hi Bas, Great Job, its really helped me lot to prepare our Income statement in PBI. Thank you so much.

@HowtoPowerBI

7 ай бұрын

Glad to hear that!!! 😎👊

Great and very useful video! Pity that PBI doesn't have actual P&L visual....it is one of the most important Business "things" in the world... Anyways I liked your approach, very good, similar to Sam's from DNA but still you covered it on detailed level. It is going to be of great use! =)

Hello Bas! Thanks a lot for the great video. I would like to add YTD and PYTD columns and the very right side of the report. I've created separate measures for that, but when I add them to the matrix , they automatically go under "Value" and show the YTD beside each month column. Please advise how to solve this?

Great Video, thanks. I am hoping you are showing a custom visual in the next video, such as Zebra BI.

Great!

This is a very informational video! Instead of a Previous Month Column, how would I do a YTD Column? But would be based on the year and period that are selected. So period selected would be the max date

Hi Bas, thank you for your Videos they are great! 👍😁 One Question, can you put that FORMAT / PosNeg thing around the SWITCH one time instead of several times for each item? Because every item has the same Numberformat. That would make the Formula much shorter. Nevertheless thank for that nice shortcut you have shown putting the posneg thing behind each item! 👍😄

Thank you this is awesome video. Could you please do a video on allocations and displaying it in the format that you did above. In my company for eg we need to take the total marketing spend and allocate it every program offered at that location by the number of student starts. I have been able to create an allocation measure but when added to the table or matrix it does not show totals for each row.

I love your tutorials, they've been an incredible help for me so far! :) May I ask you how you record your videos? Camera, program? Thanks in advance!

@parajf

2 жыл бұрын

hi, Sales figure need correction

Thanks Bas - great video. How would you add year to date into the measure?

Great. Exactly what I wanted, Can you please add a link or video on "when clicking on a selected line item description", it should retrieve the corresponding information from another table..

thank you very much as always for your videos. How can we set up a multi-level reclassified that can work with a matrix without customizing the measure?

Thanks so much for the video! I did have an additional question - if you had multiple years worth of data and wanted to have an overarching header for each year with the months as column names below it, how would you associate the year to the month columns so when you horizontally scrolled the year headers would remain with the appropriate months?

Waiting for a follow up to this. You mentioned other techniques to do this.

Thank you for creating these very informative videos, Bas. I really enjoy all the knowledge you share!

Hi Bas, thanks a lot for this tutorial. Maybe you or someone can help with an issue I have - The Matrix rows for items that are referring to other line items (such as COGS, Income before Tax, Net Income, etc.) remain blank. I assume this is because the value on their ""components" (like COGS -labour) isn't actually selected when using the SelectedValue function for the summary lines. How did it work in the video and how can I overcome this?

Excellent video. Can I have the sample excel sheet to practice along with the coding to check it out

Great video - THANK YOU! One of my challenges is the desire to have a blank row(space) between subtotals. The first part of your video using tables -seemed you were able to overcome. But somewhere at the end utilizing the matrix - the blank row between subtotals disappeared. Also, would it be possible to get a copy of the file for our better understanding? You hit a very needed topic!

@MrNyamwanga

2 жыл бұрын

Try this Link too might help. kzread.info/dash/bejne/l2amkrCih8u5cc4.html

Dude is a freaking magician wth

@HowtoPowerBI

2 жыл бұрын

:):) thanks!

We can apply to Headers -> Row subtotals -> Apply to labels

Any video regarding day to day opening and closing balance calculation i am struggling with it

Hello, sorry for the inconvenience. This video is great but, at some point do you connect the financial value table with the financial template? It is that I am trying to do it and the formula does not give me any error but it does not show me any value either. Thanks

Superb, how did you remember all those, hat off!!

@HowtoPowerBI

2 жыл бұрын

Haha :) made some notes before

This is awesome and well simplified. Is there a material/accesible documentation for step by atep approach?

@HowtoPowerBI

2 жыл бұрын

Thanks David 😊 just this video

Thanks for this video. Is there a way to actually drill through the measures that you create to get a breakdown of the figures that make up for example "Sales"? I noticed that you cant because measures can't be drilled through?

Thanks for the videos Bas. I’m a big fan of Power BI for reporting data but after watching this video, I’m surprised at how difficult it is to report financials with Power BI. I’m happy to be convinced otherwise but Excel seems like the obvious tool for this job. Thoughts anyone?

@Lotaristo

Жыл бұрын

After making a financial report for ma project I hope that i won't need to do it in any nearest future :)

great tutorial , it help me alot to build my financial report, however when i published the report, it got an error and the report was not able to be generated since i believe there was too many DAX calculations in the report; the error is "Visual has exceeded the available resources", do you have any workaround for this?

Hi Excellent explanation, is it possible to get details -I mean, when click on a item, it should display the details of a items.

Great post. But If I click on Sales, COGS, Material from financial structure table.. I need full detalils of Sales data, COGS data with selected fields in another table. How can I achieve this ?

AMAZING! Nice. Would you be able to add a Trailing-Twelve-Month's filter on the report so each month is always "TTM"? That would be a cool video or #short!

@susasebastian

2 жыл бұрын

You can calculate a TTM with the Quick Measures feature, look for the “Rolling Average” calculation and then create your measures according to your model.

Great explanation and surely very helpful for all Power BI fans, many thanks for that. But I have an another problem: we have our financial values booked on separate accounts. These separates accounts are nested in different nodes all together in a huge hierarchy (many different hierarchies). Each hierarchy is maintaned at separate MDM application and will be changed regularly (additional accounts, new nodes or just movements of an existing account to the other node). Each hierarchy will be loaded into Power BI or a source of Power BI and must be presented at a balance sheet or P&L financial reports in Power BI daily. How can I quickly create such a balance sheet (or other typical financial report) with all the nested accounts with a possibility to drill down from the upper node to the each separate account all at a one sheet? This is the standard functionality of SAP BI and SAP BO Analysis. Is it possible to implement such reporting requirements in Power BI (and azure data pool as a data source) without SAP BI and SAP BO Analisys?

Can we able to apply slicers for this also ? Kindly let me know if i have projects in my dataset can i able to slice the dashboard on the basis of projects ?

Love it! Bought a book, but this is better. ❤ Any ideas how I could show a budget and real next to eachother in this format?

@HowtoPowerBI

7 ай бұрын

thanks! :p which book? lol .. yes, just put another measure with switch on it that pulls in the values for the budget

@sjetterboks

7 ай бұрын

@@HowtoPowerBIhaha, I should have ellaborated more. Power BI voor financials is the book. I got it to work! Thanks to your files.

@HowtoPowerBI

7 ай бұрын

yey 🎉😁

is using tabular server, how would you deal with the sorting problem?

Thanks for the video! It has been very helpful. The only issue I found is when trying to format the subtotals, the conditional formatting doesn't allow me to select the highlight column to create the rule from the template made in excel. Does anyone know why this could be? Thanks in advance!

@Sigma683

Жыл бұрын

I had the same problem. I solved it by creating the following measure and applying the condtional formating to the field: P/L Values Highlight = SELECTEDVALUE ( 'PL_Template'[Highlight] )

How do you highlight the line item names (row)? When conditional formatting, mine is applied to values only

This is awesome! So I followed along, but ran into a problem when I tried to add my Prior Month column. I tried to add another SWITCH measure for Prior Month but it exceeded the resources for the visual? Since a Balance Sheet is supposed to be cumulative, how could I work around this issue? Because I can't just use "previous month". Current Month = all previous periods; Prior Month = all previous periods less the current month.

can u make same report using field parameter, which is recently introduced by powerbi

Hello, Is it possible to create a line chart inside 100% Stacked Column Chart Visual? If yes, request if you can please explain the process. Regards

great video, but spaces don't get deleted if you turn off the word wrap function

Hi Bas, thank you for this very informative video. I have a similar idea for a table in one of my reports, however for the visual you see around min 10 in your video, I would also like to get a Total value which is the sum of the above. How could i do this?

@Lotaristo

Жыл бұрын

You can just add another row called Total in the end of your Excel-table sheet and then just add another measure to summarize all values.

Hi! Amazing video. I am trying your approach but I am really wondering how you connect to the disconnected table without using any virtual relation (using relationship for instance) or a real relationship. I had to use treatas, but the next happened were that I could not use the COGS sum of the 3 other COGS items. Can you shortly just how you did this? And an extra thing, I could not get the indent spaces in my visual even though I did as you did in the video. But thanks for a great video

@victormadoya1354

2 жыл бұрын

I have the same problem. Pls assist

@fernandoguajardo6913

2 жыл бұрын

You don't need a relationship, the template elements don't apply any filter context to the measures. The drawback of this approach is that you can't access details using drill through.

Big fan of the chanel, you're my guru! One thing I'm struggling with in the Financial Value is the fact that I need to calculate for example Gross Margin (I see you have Gross Margin as a line in the data, but I don't). I have gross margin as a line in the report structure, and I have a measure for Gross Margin (sales minus cogs). If I add Gross Margin with it's formula in the financial value, it gives me no result. Can't figure it out... help? ;-)

Question: I implemented the same with 17 columns (Actual, Budget, YTD, etc), however, this method affects the performance and it takes about 40 sec to load the visual. Is there a way I can optimize that?

@wisdomwave9605

Жыл бұрын

Hey, @techfraser7880, I am having same issue. Did you find a solution?