Akshay Waghavakar on LinkedIn: 📊 Analytics Tip: Calculating Last Year Sales in Power BI using DAX… (2024)

Power BI Scenario-Based Questions: #Day2Hello Everyone!!!🤔 You have a table named Sales with columns Sales Amount, Product Category, and Sale Date. Compare the total sales for each product category against the previous year’s sales.💡 Approach-1:Sales Comparison =VAR CurrentYearSales = SUM(Sales[Sales Amount])VAR PreviousYearSales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR(Sales[Sale Date]))RETURNCurrentYearSales - PreviousYearSales💡 Approach-2:Sales Comparison =VAR CurrentYearSales = SUM(Sales[Sales Amount])VAR PreviousYearSales = CALCULATE(SUM(Sales[Sales Amount]),PARALLELPERIOD(Sales[Sale Date], -1, YEAR))RETURNCurrentYearSales - PreviousYearSales🔑 Key Points:* VAR keyword is used to define variables within a DAX expression.Syntax: VAR <VariableName> = <Expression>RETURN <ResultExpression>* SAMEPERIODLASTYEAR calculates a value for the same period in the previous yearSyntax: SAMEPERIODLASTYEAR(<dates>)* PARALLELPERIOD shifts the date context by a specified number of periods, which can be useful for comparing sales over different periods.Syntax: PARALLELPERIOD(<dates>, <number_of_periods>, <period>)..#Day1 - https://lnkd.in/dnk_uwNaFeel free to share your answers and insights in the comments.Follow Krishna Sameera Kota for regular updates on Data Analytics.Thank you! Have a nice day!...Codebasics Dhaval Patel Hemanand Vadivel Shakra Shamim Shashank Singh 🇮🇳 Munna Das Thodupunuri Bharath MADHU THANGELLA Raghavan PVenkata Naga Sai Kumar Bysani Shail Sahu Karthimeena Rangarajan Priyanka SG Arun R. AMAN KUMAR #PowerBI #PowerBIQuestions #DAX #scenarios #PracticeMakesPerfect #InterviewPrep #learntogether #careerdevelopment #dataanalytics #interviewquestions #practice #consistencymatters

122

13 Comments

When to Use Basic Aggregate functions and their X variants in Power BI (DAX)As a beginner, you might find it tricky to know when and how to use basic aggregate functions like AVERAGE and their X variants such as AVERAGEX in Power BI. Understanding their appropriate use can significantly impact the accuracy of your analysis. Let me guide you through it😁AVERAGE: This function is straightforward. It calculates the average of an entire column in a table.Example: Imagine you have a sales table with amount and quantity columns. Using AVERAGE on the amount column would give you the average of all sales amounts. Formula: Average_amount = AVERAGE(Sales[Amount])AVERAGEX: This function is more flexible and powerful. It iterates over a table (or a table expression) and evaluates an expression for each row, then returns the average of those evaluated values.Example: Suppose you have a sales table with product category and amount columns, and you need to calculate the average amount based on categories. This is where AVERAGEX shines. It’s perfect for scenarios where the value to average involves more complex expressions rather than a simple column reference.Formula: Average_amount_per_category = AVERAGEX(VALUES(Sales[Category]), [TotalSales])Other Basic Aggregate Functions and Their X Variants:SUM vs. SUMX, COUNT vs. COUNTX, COUNTA vs. COUNTAx, MIN vs. MINX, MAX vs. MAXX, MEDIAN vs. MEDIANX, STDEV vs. STDEVX, VAR vs. VARX. Just like AVERAGE VS AVERAGEX, these functions have similarities in their formulas and usage.I hope you found this helpful. #dataanalysis #dataanalytics #datascience #dax #machinelearning

5

Understanding AVERAGE vs. AVERAGEA in Power BIEver wondered why your average calculations in Power BI don’t always match up? Let's break down the key differences between AVERAGE and AVERAGEA:🧮 AVERAGE FunctionWhat it does: Calculates the mean of numeric values in a column.Ignores: Text, logical values (TRUE/FALSE), and blanks.Example:= AVERAGE(InternetSales[ExtendedSalesAmount]) 📊 AVERAGEA FunctionWhat it does: Calculates the mean, including numbers, text, and logical values.Handles non-numeric data:Values that evaluate to TRUE count as 1.Values that evaluate to FALSE count as 0.Non-numeric text counts as 0.Empty text ("") counts as 0.Example:= AVERAGEA([Amount]) Example:Imagine you have the following data:Transaction 1: Amount = 1Transaction 2: Amount = 20Transaction 3: Amount = n/a (non-numeric text)Transaction 4: Amount = (blank)Transaction 5: Amount = TRUEResults:AVERAGE of Amount: (1+20)/2=10.5(1+20)/2=10.5AVERAGEA of Amount: (1+20+0+0+1)/5=4.4(1+20+0+0+1)/5=4.4Quick Tip:Use AVERAGE: For pure numeric columns.Use AVERAGEA: When your column has a mix of numbers, logical values, and text.Next time you need to calculate averages, make sure to pick the right function for accurate results! 💡#PowerBI #DataAnalytics #BusinessIntelligence #DataScience #PowerTips

3

💡𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞𝐝 𝐂𝐨𝐥𝐮𝐦𝐧 𝐯𝐬. 𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞𝐝 𝐌𝐞𝐚𝐬𝐮𝐫𝐞: 𝐖𝐡𝐞𝐧 𝐭𝐨 𝐔𝐬𝐞 𝐄𝐚𝐜𝐡?Ever wondered whether to go with a calculated column or a calculated measure in Power BI? Here's a simple way to decide! 👇🔹 𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞𝐝 𝐂𝐨𝐥𝐮𝐦𝐧:✅ Use when you need to create new data that’s stored in the table for every row.✅ Works well when filtering or slicing the data by specific attributes.🔹 𝐄𝐱𝐚𝐦𝐩𝐥𝐞:Imagine you have a sales table and want to create a Profit Margin column for each product.🔄 Formula: (Sales - Cost) / SalesThis is a calculated column because it’s a value you want available for every single row in your table.🔹 𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞𝐝 𝐌𝐞𝐚𝐬𝐮𝐫𝐞:✅ Use when you want to perform calculations based on user interactions (like slicing, filtering, or aggregating data).✅ Measures only calculate when they’re used in visuals.🔹𝐄𝐱𝐚𝐦𝐩𝐥𝐞:Say you want to calculate Total Profit Margin across all products based on the current filters applied in your report.🔄 Formula: SUM(Sales) - SUM(Cost) / SUM(Sales)This is a calculated measure because it’s a dynamic result that changes based on filters and slicers in your report.🔑 𝐊𝐞𝐲 𝐃𝐢𝐟𝐟𝐞𝐫𝐞𝐧𝐜𝐞:- Calculated columns are pre-calculated and stored in the data model for each row.- Measures are calculated on the fly, based on the current context.Hope this helps! 😊 Have you faced any interesting challenges with calculated columns or measures in your Power BI projects? I'd love to hear your thoughts!#PowerBI #DataAnalysis #CalculatedColumns #CalculatedMeasures #DAX #DataAnalytics #PowerBItips

13

The DATEADD function in DAX (Data Analysis Expressions) in Power BI is used to add or subtract a specified number of intervals (such as days, months, quarters, or years) to a given date and return the resulting date.Here's the syntax of the DATEADD function:DATEADD(<start_date_column>, <number_of_intervals>, <interval>)- `<start_date_column>: This is the column containing the starting date to which you want to add intervals.- <number_of_intervals>: This is the number of intervals (e.g., days, months, years) to add or subtract. It can be positive to add intervals or negative to subtract intervals.- <interval>: This specifies the type of interval to add or subtract. It can be "day", "month", "quarter", or "year".Here's an example of how to use the DATEADD function in Power BI DAX:Suppose you have a Sales table with a column named "OrderDate" containing the date of each order. You want to create a new calculated column that adds 7 days to each order date.NewOrderDate = DATEADD(Sales[OrderDate], 7, DAY)This will create a new column named "NewOrderDate" in your Sales table, where each date is increased by 7 days.Similarly, you can subtract intervals by providing a negative number in the `<number_of_intervals>` parameter. For example, to subtract 1 month from the "OrderDate" column:NewOrderDate = DATEADD(Sales[OrderDate], -1, MONTH)This will create a new column where each date is reduced by 1 month.Keep in mind that the DATEADD function respects the calendar hierarchy in Power BI, so if your data is structured with a calendar hierarchy, the function will adjust dates accordingly within that hierarchy (e.g., considering leap years, end of month adjustments, etc.).#dax #functions #powerbideveloper #analysis

1

1 Comment

Power BI Scenario-Based Questions: Daily Insights #Day1Hello Everyone!!!I'm starting a new series to share scenario-based questions to challenge and enhance our Power BI skills.I hope these questions aid in your preparation. Feel free to share your answers and insights in the comments.🤔 You have a sales table with columns for Order Date, Sales Amount, and Region. Create a measure that calculates the average sales amount for the last 3 months. 💡 Approach-1:Average Sales Last 3 Months =CALCULATE(AVERAGE(Sales[Sales Amount]),DATESINPERIOD(Sales[Order Date], MAX(Sales[Order Date]), -3, MONTH))💡 Approach-2:Average Sales Last 3 Months =AVERAGEX(DATESINPERIOD(Sales[Order Date], MAX(Sales[Order Date]), -3, MONTH),[Sales Amount])🔑 Key Points:* DATESINPERIOD returns a table containing a set of dates that begin with a specified start date and continue for a specified number of intervals.Syntax: DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)In this case, it returns a table of dates within the last 3 months from the most recent date in the Order Date column. * CALCULATE function is used to modify the filter context and perform the calculation based on the specified conditions.Syntax: CALCULATE(<expression>, <filter1>, <filter2>, ...)*AVERAGEX function allows you to perform row-wise calculations and then take the average of those results.Syntax: AVERAGEX(<table>, <expression>)..Follow Krishna Sameera Kota for regular updates on Data Analytics.Thank you! Have a nice day!...Codebasics Dhaval Patel Hemanand Vadivel Shakra Shamim Shashank Singh 🇮🇳 Munna Das Thodupunuri Bharath MADHU THANGELLA Raghavan P Venkata Naga Sai Kumar Bysani Shail Sahu Karthimeena Rangarajan Priyanka SG Arun R. AMAN KUMAR #PowerBI #PowerBIQuestions #DAX #scenarios #PracticeMakesPerfect #InterviewPrep #learntogether #careerdevelopment #dataanalytics #interviewquestions #interviewpreparation #practice #consistencymatters

98

4 Comments

🌟 Excited to share some insights on Power BI! Check out my latest blog post on YTD (Year-to-Date) and YoY (Year-over-Year) calculations. Learn how to leverage these powerful features in your analytics journey: https://lnkd.in/drSsiWgf#PowerBI #DataAnalytics #YearToDate #YearOverYear #businessintelligence #dax #daxfunctions MagnusMinds IT Solution

24

This week we have deep dive and took the formatting of the visuals to next level. This blog is centred around the possibilities that RankX and TopN functions provide in Power BI.#powerbidesktop #powerbihttps://lnkd.in/gQNBDHnZ

The DATESINPERIOD function in Power BI DAX is used to generate a table of dates that spans a specified time period. This function is commonly used to create date tables or to filter data within a specific date range.Here's the syntax of the DATESINPERIOD function:DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)- `<dates>: This is a column or expression that returns a list of dates. It typically refers to a date column in your dataset or a function that generates a list of dates.- <start_date>: This is the starting date of the period.- <number_of_intervals>: This is the number of intervals to include in the period.- <interval>: This specifies the type of interval to use (e.g., "day", "month", "quarter", "year")-----------------------------------------------------------------Here's an example of how to use the DATESINPERIOD function to generate a table of dates for the current month:DatesInCurrentMonth = DATESINPERIOD('Date'[Date], TODAY(), 0, MONTH)This will create a table of dates for the current month based on the 'Date' column in your dataset.Similarly, you can specify different intervals to generate tables of dates for different time periods. For example, to generate a table of dates for the last 3 months:DatesLast3Months = DATESINPERIOD('Date'[Date], TODAY(), -3, MONTH)This will create a table of dates spanning the last 3 months.The DATESINPERIOD function is useful for creating dynamic date ranges for analysis and visualization in Power BI reports and dashboards.#dax #powerbi #analytics #linkdeincommunity #journey #improvement

6