M.Y. BI and Analytics

  Two years ago I have shared a video regarding a method on how to use DAX formulas to create the Combined Row level Security and Column level Security solution without external tool for an organization hierarchy which has mixed solid line managers and Dot line managers. If you have no chance to watch the videos please refer to the links in this video descriptions.  In that shared power BI report, due to the inflexible DAX formula to calculate the dot line manager path in the fact table “Employee Hierarchy Table”, the solution has limitations to handle more than one dot line managers parallelly in organization hierarchy, such scenarios In my practices could be that we have the different types of Dot line managers in the organization hierarchy, for example Financial Controllers and HR managers etc. they might be in same or even different layers to backup each other, and further the FC can view all sales and cost related figures under his/her layer but not above, HR can view sal...

Combine the RSI tool with other technical analytic tool, such as Candlestick tool, can help us to reach more accurate analysed result. In Power BI we can easy to draw the Candlestick chart with custom visual and present it with the RSI chart in a sample report page. But such combined charts in same page sometimes may cause an issue to sync the two charts by the date base at X axis. In the attached  video I have shared an approach to create necessary DAX measures to build Candlestick chart via power BI standard line & Stacked Column visual by which we can overcome the problem. To know more about the approach please watch the demostrations from the published video.

RSI or Relative Strength Index developed by J. Welles Wilder Jr. in 1978 is a popular tool used in technical analysis to gauge momentum and identify potential overbought or oversold conditions in a stock's price movement. It is a “momentum indicator” that averages price gains and losses during a specific trading period, It helps determine whether a stock is more likely to continue its current trend or change direction. It considers daily stock gains and losses, providing a value between 0 and 100, with the equilibrium level at 50. The Interpretation of RSI Levels:   - Overbought (RSI > 70): Indicates that the stock is overbought, and the upward trend may reverse soon. Selling could be prudent. - Oversold (RSI < 30): Suggests that the stock is oversold, and the downward trend may reverse. Buying opportunities may arise. There are  different types of RSI based on its moving average calculations. Here I summarize four typic types of RSI in shortlist as following: ...

 

  The video shares a method by running R script in power query to calculate MACD (Moving Average Convergence Divergence) Indicator with Assumed Initial Value which come from the first closing price data point. In video the advantages and disadvantages of the two MACD calculations, one with initials and one without initials, are compared in a table list. The calculation R scripts are well explained in details with each line in the video as well. You may find and copy the R code lines in my blog site which URL is attached in following. The R code lines as: # Step 1: Load dataset and format data  Dataset <- dataset # format date type as expected date format day/month/year Dataset$Date <- as.Date(Dataset$Date, format = "%d/%m/%Y") # Ensure close_price is numeric Dataset$Close <- as.numeric(Dataset$Close)   # Step 2: Creat Function to calculate EMA calculate_EMA <- function(x, n = 9) {   multiplier <- 2 / (n + 1)   ema <- numeric(length(x)) ...

  In this Video I shall share a method on using R in Power BI desktop to unzip the compressed excel files based on the identified file name extensions of extracted files, such as csv, xlsx, xls and xlsb etc, then combine them if they have same column headers and table structure, Finally load the combined data frame into power BI for further data modelling. The introduced R script in this video only focus on loading zipped files from local machine but it is possible to have slight modifications on the R script to be able to fulfil such automatic features online at cloud sites with the required respective http URL paths and access logins.   Let’s get start:   First, we need to prepare the received compressed sample files, we have four excel files with extensions, csv, xls, xlsx and  xlsb. All of them have the same data table structure, same column numbers with same column headers. After compressing them we get a zipped folder for the zipped four files as “Downl...