M.Y. BI and Analytics

The entire code shared in this video is designed to automate the process of creating a dynamic line chart based on loan data in the "Loan Report" sheet and display the chart on the "Charts" sheet. The chart shows trends in accumulated principal, accumulated interest, and loan remaining balance over time. The code is also flexible enough to handle both yearly and monthly loan schedule reports. The VBA codes as: Sub CreateLineChart()       '1. Variable Declarations     Dim srcSheet As Worksheet     Dim chtSheet As Worksheet     Dim lastRow As Long     Dim chartObj As Shape     Dim chartDataRange As Range     Dim xAxisRange As Range     Dim yAxisRange As Range     Dim colPrincipal As Variant     Dim colInterest As Variant     Dim colRemainingBalance, ColMonth As Variant     '2. set source and chart sheets     Set srcSheet = ThisWorkbook.Worksheets("Loa...

Business Case raised by my youtube channel viewer on the 21st July 2023. In my employee tenure data, it has employee birthday, hire date and leaving date, and race, gender, etc. Do you have any idea how to create a dynamic measure of tenure and/or age, as well as a "tenure group" and "age group" that can change with the calendar date? I would like to show in a line chart the changing number of active employees across time (as x-axis). Furthermore, I would like to breakdown the number of employees by their age group or tenure group (using age group as a legend). The age or tenure group will be dynamically change according to the dates in the X-axis. Any suggestions are appreciated. My suggestions: Assumptions on what we have for the case:                         - an employee table with coloumns: table update(or registered) date,              ...

In this video, to provide users with a summarized yearly loan schedule report, I guide viewers in creating a new VBA macro based on modifying my previous Video demonstrated VBA macro for monthly loan schedule report which reveals interest and principal payments over the long term. The process involves two vertical VBA project windows and adjusting variables, calculations, and formatting. The updated procedure is offering an aggregated yearly report for different loan terms. Ths VBA codes is provided as following: Sub GenerateLoanReport_AggregatedYearlyPeriodReport() ' Created by MIKE GuangYuan YU on 8th of July , 2023 for M.Y BI & Analytics youtube channel     ' the procedure is to create loan report at yearly repayment period     ' Version 1.0          'I. Define required variables         Dim wsLoan, wsLoanReport As Worksheet         Dim LoanAmount As Double         Dim Interes...

  Loan calculations involve using mathematical formulas to determine important loan aspects such as monthly payments, total interest, and overall cost. They are essential for financial planning and widely used by individuals, businesses, and financial institutions. Excel provides powerful loan calculation functions like PMT, PPMT, and IPMT, enabling users to analyze loans effectively. In my Excel example, these functions are utilized to create a basic Loan Calculator, where users input loan details and obtain calculated results for monthly and final repayments. Additionally, Excel's VBA codes can automate the generation of long-term loan reports, providing comprehensive information on each payment period. Enabling the Developer tab and accessing VBA procedures allows users to streamline and expedite loan analysis. My Excel VBA codes can automate the generation of long-term loan reports: Sub GenerateLoanReport_monthlyPeriodReport()          ' Created by MIKE...

  DAX Formula for Video:  Account ranking index for top N number =      Var Selected_TOP_N_Number = SELECTEDVALUE('Table for Top N Selections'[Top N]) RETURN if( [Account value rank Index]<=Selected_TOP_N_Number, VALUE(1), VALUE(0)     )

  The Youtube Vidoe:  Power BI DAX: Dynamic Ranking Calculations Based on User Selections and Filters         DAX formulas from the Video: Total Opp count = DISTINCTCOUNTNOBLANK('Fact'[Opportunity ID]) Total P_Revenue = SUM('Fact'[ProductRevenue]) Total F_P_Revenue = SUM('Fact'[FactoredProductRevenue]) Selected KPIs = SELECTEDVALUE('Table_KPIs'[KPI Name]) Account value rank index =      SWITCH(         TRUE(),         [Selected KPIs]="Opportunity Numbers",RANKX(ALL('Account'[Account]),[Total Opp count],,,Dense),         [Selected KPIs]="Product Revenues",RANKX(ALL('Account'[Account]),[Total P_Revenue],,,Dense),         [Selected KPIs]="Factored Product Revenues",RANKX(ALL('Account'[Account]),[Total F_P_Revenue],,,Dense)     ) Account KPI values for sorting =      SWITCH(         TRUE(),         ...

The DAX formula codes suggested in the Video are :  1). Avg BP (Joint) Status =          SWITCH(             TRUE(),             [Average Systolic BP(Visual Total)] >=[Systolic_Max] || [Average Diastolic BP (Visual Total)]>=[Diastolic_Max], "Hypertnsion",             [Average Systolic BP(Visual Total)]<[Systolic_Max] && [Average Systolic BP(Visual Total)] >[Systolic_Min]                  && [Average Diastolic BP (Visual Total)]<[Diastolic_Max] && [Average Diastolic BP (Visual Total)] >[Diastolic_Min], "Normal",              [Average Systolic BP(Visual Total)]<=[Systolic_Min] || [Average Diastolic BP (Visual Total)]<=[Diastolic_Min] , "Low BP"         ) 2). Avg BP (Joint) Colour =          SWITCH( ...