Power BI Tips & Tricks

 Calculate Sales last day of each month Problem Scenario: Monthly Portfolio Reporting Background: You work as a financial analyst at a large investment firm. Your team is responsible for analyzing the performance of various investment portfolios on a monthly basis. Each portfolio consists of a diverse range of assets including stocks, bonds, and mutual funds. Problem Statement: Your team needs to generate monthly reports summarizing the performance of each investment portfolio. The reports should include key metrics such as total portfolio value, asset allocation, and performance returns. However, the challenge lies in identifying the latest available data for each month to ensure accurate reporting. Dax Function: Lastdateineachmonth = CALCULATE( MAX('Sales'[Date]), ALLEXCEPT('Sales', 'Sales'[Month]) ) Dax Explanation: CALCULATE Function: CALCULATE is a powerful DAX function used for modifying the context in which other functions are ev...

 Last 12 Months from Today Dax Formula: Last12Months =  IF (     AND (         'Calendar Table'[Date] >= EDATE(TODAY(), -11),         'Calendar Table'[Date] <= TODAY()     ),     1,     0 ) Explanation: This function appears to be designed to create a flag or indicator for dates falling within the last 12 months relative to the current date. Here's a breakdown of how the function works: 1) IF Statement: The function starts with an IF statement, which evaluates a condition and returns one value if the condition is true, and another value if the condition is false. 2) AND Function: Within the IF statement, there's an AND function, which checks if all of its arguments are true. In this case, it's checking two conditions. 3) EDATE Function: The EDATE function is used to calculate a date that is a specified number of months before or after another date. EDATE(TODAY(), -11) calculates the date that...

Time Intelligence Dax Functions - Power BI TOTALYTD: Calculates the year-to-date total for a given expression. Example:  Total Sales YTD = TOTALYTD(SUM('Sales'[Amount]), 'Date'[Date]) TOTALQTD: Calculates the quarter-to-date total for a given expression. Example:  Total Sales QTD = TOTALQTD(SUM('Sales'[Amount]), 'Date'[Date]) TOTALMTD: Calculates the month-to-date total for a given expression. Example:  Total Sales MTD = TOTALMTD(SUM('Sales'[Amount]), 'Date'[Date]) DATESYTD: Returns a table that contains all dates from the beginning of the year up to the specified date. Example: Sales YTD = CALCULATE(SUM('Sales'[Amount]), DATESYTD('Date'[Date])) DATESQTD: Returns a table that contains all dates from the beginning of the quarter up to the specified date. Example: Sales QTD = CALCULATE(SUM('Sales'[Amount]), DATESQTD('Date'[Date])) DATESMTD: Returns a table that contains all dates from the beginning of the mon...

Sameperiodlastyear till to date based on selection in year and month selection Problem:  If the current year and month are selected, sales data from the same period of the previous year up to the current date is needed. For instance, if it's March 2024, you'd require sales data from March 2023 up to today's date. If the selection is for a different year and month, such as January 2023, you need the entire sales data for January 2022. Solution:  For this scenario you need to create Three Dax Measures Measure 1 = (This measure is to calculate current month sales till the date) CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) ) Explanation: CALCULATE : This is a DAX function used to modify or filter the context for a calculation. It can apply additional filters to a ...

How to create calendar table by using power query Step 1: Click on Get Data Step 2: Select Blank Query Step 3: Click on Advance Editor in the Home Tab of Power Query Editor Step 4: Remove the syntax and paste below mentioned syntax Syntax: Paste below mentioned syntax let     StartDate = #date(2024, 1, 1),     EndDate = #date(2024, 12, 31),     NumberOfDays = Duration.Days(EndDate - StartDate),     DateList = List.Dates(StartDate, NumberOfDays+1, #duration(1, 0, 0, 0)),     TableFromList = Table.FromList(DateList, Splitter.SplitByNothing()) in     TableFromList If you want a calendar for a different range, modify the StartDate and EndDate variables accordingly. Step 5: Click "Done" in the Advanced Editor. Step 6: You may want to add additional columns to your calendar table, such as Year, Month, Quarter, Day of Week, etc. Here's an example of how to add a Year column: In Power Query Editor, select the "Date" column. Go to th...

 Calendar Table using Dax Syntax: CalendarTable = ADDCOLUMNS ( CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "DayOfWeek", WEEKDAY([Date]), "DayOfMonth", DAY([Date]), "WeekdayName", FORMAT([Date], "dddd"), "IsWeekend", IF( OR(WEEKDAY([Date]) = 1, WEEKDAY([Date]) = 7), TRUE(), FALSE()), "IsHoliday", IF( OR( MONTH([Date]) = 1 && DAY([Date]) = 1, MONTH([Date]) = 7 && DAY([Date]) = 4), TRUE(), FALSE()) ) Explanation: CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)) Creates a table with a single column containing dates from January 1, 2024, to December 31, 2024. ADDCOLUMNS ( Adds new columns to the table created by the CALENDAR function. "Year", YEAR([Date]), Adds a column named "Year" that...