![]() ![]() This will calculate the running total from total amount.įig: Running Total Comparison without filterįig: Running Total Comparison with filter Month Amount = calculate(sum(Running_Total),filter(all(Running_Total,Running_Total),Running_Total <= max(Running_Total))) This will display the month in numeric value. ![]() This will display the month in the first three letter abbreviation format. This will display the month with first 3 letter abbreviation and year in 4 digit format. MonYr = Running_Total &" "& Running_Total This will calculate and display the Year and Month together in numeric value. This will get the Warehouse code with relation to the warehouse code in the table Warehouse. To get the sales running total report, which can be filtered on the basis of warehouse, Team manager & Beat level. 1st day of previous month vs 1st day of current month. 1st Monday of previous month vs 1st Monday of current month.īelow report compares sales figure by date of previous and current month. This is to calculate the Total Sales in Current month.īelow report compares sales figure by weekday of previous and current month. This is to calculate the Total Sales in Previous month.Ĭurrent Month Total Sales = CALCULATE (SUM (Modified_Sales), filter(Modified_Sales,Modified_Sales = Modified_Sales)) Previous Month Total Sales = CALCULATE (SUM (Modified_Sales), FILTER(Modified_Sales, Modified_Sales=Modified_Sales)) This will calculate the Amount in rupees and convert the USA currency into Rupees, thus all the amount will be in one currency. Month Week Name = Modified_Sales & "-"&Modified_Sales Īmount = if (Modified_Sales = "USA",Modified_Sales*64.80,Modified_Sales) This will calculate the month’s week number. MonthWeek = if(Modified_Sales >= 1 & Modified_Sales = 1 & Modified_Sales = 1 & Modified_Sales = 2 & Modified_Sales = 1 & Modified_Sales = 8 & Modified_Sales = 1 & Modified_Sales = 15 & Modified_Sales <= 28,"4","5") ))) This will calculate which week of the year is it. To derive the week days in words using switch function. This weekday column will display the week day in numeric considering Monday as 1.ĭay Name = switch (Modified_Sales, 1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thrusday",5,"Friday",6,"Saturday",7,"Sunday") This is to calculate which day of sales date.Ībove DAX function is to derive warehouse name based on warehouse code. This is to calculate the Previous month dynamically, where the DAX function states that if the current month is 1 then the previous month will be 12 or else for all other months is will automatically calculate the current month minus one as the previous month. Previous Month = IF (Modified_Sales =1, 12,Modified_Sales-1) To get the comparison of sales figure by date for previous and current month, we need to use below DAX functions for creating calculated columns. In the Excel source file, we have created few sheets along with sample data as shown below: ![]() To demonstrate this concept, we have created an excel file with dummy data and connected it to Power BI Desktop Tool. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |