Power BI & the Possibilities with Financial Applications
အားလုံးပဲ မင်္ဂလာပါ။ Financial Analysis အပိုင်းကို စိတ်ဝင်စားပြီး Analysis Report များကို Automate ပြုလုပ်လိုသူများအတွက် အထောက်အကူပြုမဲ့ Webinar လေးတစ်ခုကို ယူဆောင်လာပါပြီ။ လုပ်ငန်းခွင်မှာ Financial Applications နဲ့ Power BI ကို ချိတ်ဆက်အသုံးပြုဖို့ စိတ်ဝင်စားကြတဲ့သူတွေအတွက် တက်သင့်တဲ့ Webinar လေးမို့ လာတက်ကြဖို့ ဖိတ်ခေါ်လိုက်ပါတယ်#MSFABRICBRISTOL - Feb 25 - P&L Models & Financial Reporting in PBI
Details When: Thursday 20th February 2025 Time: arrive for 5:45pm with talks starting from 6pm start prompt. Location: BJSS, 90 Victoria St, Redcliffe, BS1 6DP Complimentary drinks & pizza provided by our hosts BJSS Social drinks afterwards will be at Ye Shakespeare (50m away) for those interested. This #MSFABRICBRISTOL will feature Chris Barber and Rishi Sapra Session 1 - P&L Models - Chris Barber Profitability is a key metric; any profits can be distributed back to shareholders (owners) either directly or indirectly. A profit and loss (P&L) statement answers high level questions - such as “what was net profit last year?” - and summarises key revenue (i.e., product revenue) and expense (i.e., Research & Development) items. Unlike static reports, a P&L semantic model contains the detail; this allows end-users to ask questions such as “what was R&D spending broken down by a particular research project, fiscal period, or legal entity?” A semantic model also allows end-users to consume information using Copilot prompts, Power BI reports, or Pivot Tables and formulas in Excel. In this session, we will go through: Why you should build an income statement semantic model How to use an accelerator to speed up development. The key questions you need to ask stakeholders. The challenges in building a P&L income statement About Chris Chris Barber is a chartered accountant (ACMA, CGMA), 4 times Microsoft MVP, author of Income Statement Semantic Models, runs StarSchema.co.uk, and has popular YouTube videos (over 100k views) and courses (over 1k participants) on the topic of Profit and Loss (P&L) using Power BI. Session 2 - Financial Reporting in PBI - Rishi Sapra This session will explore how to transform a traditional Excel-based credit rating model for countries into a modern, interactive, and scalable solution using Power BI and Microsoft Fabric. Participants will discover innovative techniques to enhance financial modeling and storytelling by leveraging advanced analytics and integration capabilities. Hear from Power Platform in Finance specialist and Microsoft MVP, Rishi Sapra, as we delve into the following areas: Replicating Complex Rating Logic: Translating intricate credit rating formulas and methodologies from Excel into DAX to ensure consistency and accuracy. Interactive Insights: Using slicers, bookmarks, and contextual visualizations in Power BI to enable dynamic comparisons and highlight key drivers of credit ratings. Streamlined Reporting: Designing intuitive and user-friendly reports that empower stakeholders to explore insights interactively. Scalability with Microsoft Fabric: Harnessing the Medallion Lakehouse architecture to enable seamless data integration, metadata management, and collaboration at an enterprise level. This session is particularly beneficial for finance professionals and analysts who are seeking to modernize their financial or operational models. Attendees will leave with practical strategies to maintain model accuracy, enhance interactivity, and scale their solutions to meet the evolving demands of modern analytics. About Rishi Rishi has a unique blend of technical and commercial skills - he is a 5x Microsoft Most Valuable Professional (MVP), a Chartered Accountant, a Microsoft Certified Trainer (MCT) and an Executive MBA (Hons) holder with a first class degree from the London School of Economics. He currently works as a Data & AI Strategic Project lead at Avanade - a Joint Venture between Accenture and Microsoft which been awarded Microsoft Partner of the Year 18 times! In this role he focuses on Microsoft Fabric Go To Market (GTM) offerings including those around Security & Governance, Industry/Functional accelerators and Azure/BI migration. Prior to joining Altius in 2017 (Which was acquired by Avanade in 2020), he spent 12 years in the Big 4 /Financial Services (Deloitte, HSBC, Barclays, KPMG) and has a strong background in Business modelling and process improvement. He is actively involved in the Fabric Community in London/globally and has presented at Meetup groups and several large-scale conferences. His passion is in helping organisations achieve the holy grail of Self-Service BI and helping individuals learn the necessary skills to succeed. Under his community Learn Data Insights (www.learndatainsights.com), he runs Microsoft Fabric Training (www.msfabrictraining.com) and Power Platform Finance (www.powerplatformfinance.com) with e-learning courses and quizzes for Analytics and Finance professionals. We all look forward to seeing you there!!Power BI: Show weekend Data on Monday or Friday
Problem: I have data for all weekdays, but I do not want to show data on weekends, I would like to show the same on Monday Solution: There is a solution like creating a new date column and moving weekend to Monday or Friday and join the new column with the date. But we would like to have a solution using only measures. This means we need to move weekend data by 1–2 days on either side Columns I already have in the Model Discount = [Gross Sales]*[Dis Per]/100.0 Gross Sales = [Qty] * [Price] Net Sales = [Gross Sales] - [Discount] Measure I am using Net = Sum(Sales[Net Sales]) New measures created for weekend data moved to Monday Net non Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) >=6 )) Net non work 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],-2,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],-1,DAY)) Net Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) <6 )) Net Show on Monday = [Net Work] + [Net non work 1] New measures created for Friday Net non work b 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],1,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],2,DAY)) Net Show on Friday = [Net Work] + [Net non work b 1] Find more details on the BlogDAX Custom 445 Calendar
Thanks to a request by dogt1225 from this thread comes this highly configurable DAX Custom 445 Calendar. Now, this is certainly not the first custom calendar nor will it be the last but it is one that I created and I think it is notable because of how easily configurable it is to customize for your own needs. In this instance, the calendar is configured for weeks starting on Saturday and ending on Friday starting on the 5th Saturday of the year 2020 for 2 years. Additional features of this calendar include assigning a week # of the year, week # of the quarter, sequential week #, quarter, month, day of year, etc. Custom445 = VAR __StartYear = 2020 // starting year VAR __NumYears = 2 // number of years including start year VAR __WeekForm = 16 // 16 has Saturday as 1, Friday 7 VAR __StartDay = 1 // weekday to start calendar on VAR __StartWeek = 5 // # instance of weekday to start calendar on (5th Saturday for example) VAR __Base = CALENDAR(DATE(__StartYear,1,1),DATE(__StartYear,12,31)) VAR __StartDate = MAXX( FILTER( ADDCOLUMNS( __Base, "WeekNum",COUNTROWS(FILTER(__Base,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)) ), [WeekNum]=__StartWeek && WEEKDAY([Date],16)=__StartDay ), [Date] ) VAR __CalendarBase = CALENDAR(__StartDate,__StartDate + 52 * __NumYears * 7 - 1) VAR __Calendar = ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( __CalendarBase, "Year",ROUNDUP(([Date]-__StartDate+1)*1./ (52*7),0)-1+__StartYear, "DAYOFWK#",MOD(([Date] - __StartDate),7)+1, "SEQWK#",COUNTROWS(FILTER(__CalendarBase,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)), "DAY#YEAR",MOD(([Date]-__StartDate)*1.,(52*7))+1, "DAY",DAY([Date]) ), "WK#",ROUNDUP([DAY#YEAR]/7,0), "QWK#",MOD([SEQWK#]-1,13)+1, "Q",ROUNDUP([DAY#YEAR]/91,0) ), "Month",SWITCH(TRUE(), [Q]=1 && [QWK#]<=4,1, [Q]=1 && [QWK#]<=8,2, [Q]=1,3, [Q]=2 && [QWK#]<=4,4, [Q]=2 && [QWK#]<=8,5, [Q]=2,6, [Q]=3 && [QWK#]<=4,7, [Q]=3 && [QWK#]<=8,8, [Q]=3,9, [Q]=4 && [QWK#]<=4,10, [Q]=4 && [QWK#]<=8,11, [Q]=4,12 ) ), "MonthName", SWITCH([Month], 1,"February", 2,"March", 3,"April", 4,"May", 5,"June", 6,"July", 7,"August", 8,"September", 9,"October", 10,"November", 11,"December", 12,"January" ) ) RETURN __Calendar eyJrIjoiOTE4YTNmOWUtNTBjOS00ZjM3LWEzZjEtMTU3OTE2YjM5ZmFjIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9DAX: Get all dates between the Start and End date
Problem Statement: Data has been provided with the date range in each row. Data need to be distributed or expanded for all the dates in the range. A new solution is needed using the DAX. The old DAX solution: here, Power Query Solution- here In DAX, we will use Generate along with Calendar to create a table of the dates between two dates. In the same way, we create a calendar. But for each row and expand into multiple rows using Generate. We have table like Code for the new table with all dates between the start and end date Expanded = GENERATE(Data,CALENDAR(Data[Start date],Data[End date])) Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI. Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more. You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and shareBetter Running Total
Microsoft's running total quick measure, well, it's just not very good. It's overly complex and doesn't work in single table situations. There's a better way as shown in this video: MSHGQM - Don't Use CALCULATE! - YouTube For reference, Microsoft's running total quick measure generates code such as: Value running total in Date = CALCULATE( SUM('Table'[Value]), FILTER( ALLSELECTED('Table'[Date]), ISONORAFTER('Table'[Date], MAX('Table'[Date]), DESC) ) ) As shown, this running total doesn't work in single table situations. A better, less complex way to create a running total that works with a single table is like this: Better RT = VAR __Date = MAX('Table'[Date]) VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date) RETURN SUMX(__Table,[Value]) And with just a minor change, this method also works if you have a separate Dates table: Better RT 2 = VAR __Date = MAX('Dates'[Date]) VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date) RETURN SUMX(__Table,[Value]) Watch the video! eyJrIjoiMTcxZWJiZmEtZDdiMy00YWYyLWEyOTYtMmI1MDQ4YjlmMTY5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Power BI Interview Questions -101 Interview Questions| Power BI 101 Concepts
In today’s data-driven world, the demand for effective data analysis and visualization tools has skyrocketed, and Power BI has emerged as a leading solution in the realm of business intelligence. As organizations seek to make data-informed decisions, Power BI’s versatility and user-friendly interface have made it a top choice for professionals across various industries. For individuals preparing for a Power BI interview, demonstrating a strong grasp of the tool’s functionalities and data manipulation capabilities is essential to stand out from the competition. In this comprehensive blog post, we will delve into the most commonly asked Power BI interview questions and provide insightful answers to help you excel in your interview. As Power BI interviewers, we value both functional knowledge and strong technical expertise. We aim to ensure that candidates not only understand the concepts but have practical experience working with Power BI. To assess your hands-on experience, we may tweak questions to check the application of concepts. For instance, we might present a scenario to gauge your practical skills instead of asking a basic question about interactions. For example, we could ask: “In a Power BI report, you have a page that contains two slicers say- Slicer 1 and Slicer 2. Additionally, there are two Line visuals assuming Line Visual 1 and Line Visual 2 are on the same page. How would you make sure that Slicer 1 only filter Line Visual 1, and Slicer 2 only filter Line Visual 2?” By presenting such real-world scenarios, we aim to assess your ability to apply Power BI concepts in practical situations. Your response to this question will demonstrate your technical understanding of setting up interactions and will reveal your familiarity with the Power BI interface and functionalities. Throughout the interview, we may present similar scenarios and technical challenges to further evaluate your expertise. This approach enables us to determine your level of proficiency in Power BI. It ensures that we select candidates who not only possess theoretical knowledge but can also effectively translate that knowledge into real-life projects and reporting tasks. Organizations encourage you to come prepared with hands-on experience and be ready to showcase your practical skills. Demonstrating your ability to work with Power BI confidently will significantly enhance your chances of excelling in the interview process and becoming a valuable addition to the team. You can find the first 40 questions here https://medium.com/microsoft-power-bi/power-bi-interview-questions-part-1-4982de3be327 You can find the Next 61 questions here Power BI Interview Questions- How to Crack Power BI Interview- 101 Questions | Medium The file for the last 61 questions is attached at the end of this article.Customer Retention Part 3: Period Of Stay – Cohort Analysis
Objective: Period Of Stay – Cohort Analysis provide visibility on how many customers were retained after their first date of purchase. Cohort Analysis is studying the behavioral analysis of customers. Assume there are 100 new customers (consumers who made the first purchase in the store) in Jan 2020. Out of these 100, how many customers came back in the second month (Feb 2020). Then how many returned in the third month (March -2020) and so on for every month in 2020. Columns First Sales = minx(FILTER(Sales,[Customer Id] =EARLIER([Customer Id])),[Sales Date]) Customer Age = DATEDIFF([First Sales],[Sales Date],MONTH)+1 Table Customer Age Bucket = ADDCOLUMNS(GENERATESERIES(1,max(Sales[Customer Age])+1) ,"Age in Month" , "Month " &[Value]) Measures Customers = DISTINCTCOUNT(Sales[Customer Id]) Retain % = CALCULATE(divide(DISTINCTCOUNT(Sales[Customer Id]),CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =1)) , 'Customer Age Bucket'[Age] >1) eyJrIjoiYWM4MGY3ZTUtZmZhZS00ZDQ4LWE1NzUtMGUwMDc3N2U4MmI0IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9Power BI: Get the Last/Latest Value of a Category/Group
Power BI: Get the Last/Latest Value of a Category/Group Problem Statement: In the given data filter and show only the most recent row based on date/version etc. Data: I have taken data where we have ID and date along with status. And we would like to show the latest status. Solution: We will use a DAX measure for that. I have loaded the data to the power bi file and created the following DAX measure Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[ID] = Max(Data[ID])), Data[Date]) return CALCULATE(max(Data[Status]), filter((Data) , Data[Date] =_max)) Refer to the attached file.
