Power BI: Show weekend Data on Monday or Friday
Problem: I have data for all weekdays, but I do not want to show data on weekends, I would like to show the same on Monday Solution: There is a solution like creating a new date column and moving weekend to Monday or Friday and join the new column with the date. But we would like to have a solution using only measures. This means we need to move weekend data by 1–2 days on either side Columns I already have in the Model Discount = [Gross Sales]*[Dis Per]/100.0 Gross Sales = [Qty] * [Price] Net Sales = [Gross Sales] - [Discount] Measure I am using Net = Sum(Sales[Net Sales]) New measures created for weekend data moved to Monday Net non Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) >=6 )) Net non work 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],-2,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],-1,DAY)) Net Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) <6 )) Net Show on Monday = [Net Work] + [Net non work 1] New measures created for Friday Net non work b 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],1,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],2,DAY)) Net Show on Friday = [Net Work] + [Net non work b 1] Find more details on the BlogDAX Custom 445 Calendar
Thanks to a request by dogt1225 from this thread comes this highly configurable DAX Custom 445 Calendar. Now, this is certainly not the first custom calendar nor will it be the last but it is one that I created and I think it is notable because of how easily configurable it is to customize for your own needs. In this instance, the calendar is configured for weeks starting on Saturday and ending on Friday starting on the 5th Saturday of the year 2020 for 2 years. Additional features of this calendar include assigning a week # of the year, week # of the quarter, sequential week #, quarter, month, day of year, etc. Custom445 = VAR __StartYear = 2020 // starting year VAR __NumYears = 2 // number of years including start year VAR __WeekForm = 16 // 16 has Saturday as 1, Friday 7 VAR __StartDay = 1 // weekday to start calendar on VAR __StartWeek = 5 // # instance of weekday to start calendar on (5th Saturday for example) VAR __Base = CALENDAR(DATE(__StartYear,1,1),DATE(__StartYear,12,31)) VAR __StartDate = MAXX( FILTER( ADDCOLUMNS( __Base, "WeekNum",COUNTROWS(FILTER(__Base,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)) ), [WeekNum]=__StartWeek && WEEKDAY([Date],16)=__StartDay ), [Date] ) VAR __CalendarBase = CALENDAR(__StartDate,__StartDate + 52 * __NumYears * 7 - 1) VAR __Calendar = ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( __CalendarBase, "Year",ROUNDUP(([Date]-__StartDate+1)*1./ (52*7),0)-1+__StartYear, "DAYOFWK#",MOD(([Date] - __StartDate),7)+1, "SEQWK#",COUNTROWS(FILTER(__CalendarBase,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)), "DAY#YEAR",MOD(([Date]-__StartDate)*1.,(52*7))+1, "DAY",DAY([Date]) ), "WK#",ROUNDUP([DAY#YEAR]/7,0), "QWK#",MOD([SEQWK#]-1,13)+1, "Q",ROUNDUP([DAY#YEAR]/91,0) ), "Month",SWITCH(TRUE(), [Q]=1 && [QWK#]<=4,1, [Q]=1 && [QWK#]<=8,2, [Q]=1,3, [Q]=2 && [QWK#]<=4,4, [Q]=2 && [QWK#]<=8,5, [Q]=2,6, [Q]=3 && [QWK#]<=4,7, [Q]=3 && [QWK#]<=8,8, [Q]=3,9, [Q]=4 && [QWK#]<=4,10, [Q]=4 && [QWK#]<=8,11, [Q]=4,12 ) ), "MonthName", SWITCH([Month], 1,"February", 2,"March", 3,"April", 4,"May", 5,"June", 6,"July", 7,"August", 8,"September", 9,"October", 10,"November", 11,"December", 12,"January" ) ) RETURN __Calendar eyJrIjoiOTE4YTNmOWUtNTBjOS00ZjM3LWEzZjEtMTU3OTE2YjM5ZmFjIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9DAX: Get all dates between the Start and End date
Problem Statement: Data has been provided with the date range in each row. Data need to be distributed or expanded for all the dates in the range. A new solution is needed using the DAX. The old DAX solution: here, Power Query Solution- here In DAX, we will use Generate along with Calendar to create a table of the dates between two dates. In the same way, we create a calendar. But for each row and expand into multiple rows using Generate. We have table like Code for the new table with all dates between the start and end date Expanded = GENERATE(Data,CALENDAR(Data[Start date],Data[End date])) Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI. Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more. You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and shareBetter Running Total
Microsoft's running total quick measure, well, it's just not very good. It's overly complex and doesn't work in single table situations. There's a better way as shown in this video: MSHGQM - Don't Use CALCULATE! - YouTube For reference, Microsoft's running total quick measure generates code such as: Value running total in Date = CALCULATE( SUM('Table'[Value]), FILTER( ALLSELECTED('Table'[Date]), ISONORAFTER('Table'[Date], MAX('Table'[Date]), DESC) ) ) As shown, this running total doesn't work in single table situations. A better, less complex way to create a running total that works with a single table is like this: Better RT = VAR __Date = MAX('Table'[Date]) VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date) RETURN SUMX(__Table,[Value]) And with just a minor change, this method also works if you have a separate Dates table: Better RT 2 = VAR __Date = MAX('Dates'[Date]) VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date) RETURN SUMX(__Table,[Value]) Watch the video! eyJrIjoiMTcxZWJiZmEtZDdiMy00YWYyLWEyOTYtMmI1MDQ4YjlmMTY5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Power BI Interview Questions -101 Interview Questions| Power BI 101 Concepts
In today’s data-driven world, the demand for effective data analysis and visualization tools has skyrocketed, and Power BI has emerged as a leading solution in the realm of business intelligence. As organizations seek to make data-informed decisions, Power BI’s versatility and user-friendly interface have made it a top choice for professionals across various industries. For individuals preparing for a Power BI interview, demonstrating a strong grasp of the tool’s functionalities and data manipulation capabilities is essential to stand out from the competition. In this comprehensive blog post, we will delve into the most commonly asked Power BI interview questions and provide insightful answers to help you excel in your interview. As Power BI interviewers, we value both functional knowledge and strong technical expertise. We aim to ensure that candidates not only understand the concepts but have practical experience working with Power BI. To assess your hands-on experience, we may tweak questions to check the application of concepts. For instance, we might present a scenario to gauge your practical skills instead of asking a basic question about interactions. For example, we could ask: “In a Power BI report, you have a page that contains two slicers say- Slicer 1 and Slicer 2. Additionally, there are two Line visuals assuming Line Visual 1 and Line Visual 2 are on the same page. How would you make sure that Slicer 1 only filter Line Visual 1, and Slicer 2 only filter Line Visual 2?” By presenting such real-world scenarios, we aim to assess your ability to apply Power BI concepts in practical situations. Your response to this question will demonstrate your technical understanding of setting up interactions and will reveal your familiarity with the Power BI interface and functionalities. Throughout the interview, we may present similar scenarios and technical challenges to further evaluate your expertise. This approach enables us to determine your level of proficiency in Power BI. It ensures that we select candidates who not only possess theoretical knowledge but can also effectively translate that knowledge into real-life projects and reporting tasks. Organizations encourage you to come prepared with hands-on experience and be ready to showcase your practical skills. Demonstrating your ability to work with Power BI confidently will significantly enhance your chances of excelling in the interview process and becoming a valuable addition to the team. You can find the first 40 questions here https://medium.com/microsoft-power-bi/power-bi-interview-questions-part-1-4982de3be327 You can find the Next 61 questions here Power BI Interview Questions- How to Crack Power BI Interview- 101 Questions | Medium The file for the last 61 questions is attached at the end of this article.Customer Retention Part 3: Period Of Stay – Cohort Analysis
Objective: Period Of Stay – Cohort Analysis provide visibility on how many customers were retained after their first date of purchase. Cohort Analysis is studying the behavioral analysis of customers. Assume there are 100 new customers (consumers who made the first purchase in the store) in Jan 2020. Out of these 100, how many customers came back in the second month (Feb 2020). Then how many returned in the third month (March -2020) and so on for every month in 2020. Columns First Sales = minx(FILTER(Sales,[Customer Id] =EARLIER([Customer Id])),[Sales Date]) Customer Age = DATEDIFF([First Sales],[Sales Date],MONTH)+1 Table Customer Age Bucket = ADDCOLUMNS(GENERATESERIES(1,max(Sales[Customer Age])+1) ,"Age in Month" , "Month " &[Value]) Measures Customers = DISTINCTCOUNT(Sales[Customer Id]) Retain % = CALCULATE(divide(DISTINCTCOUNT(Sales[Customer Id]),CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =1)) , 'Customer Age Bucket'[Age] >1) eyJrIjoiYWM4MGY3ZTUtZmZhZS00ZDQ4LWE1NzUtMGUwMDc3N2U4MmI0IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9Power BI: Get the Last/Latest Value of a Category/Group
Power BI: Get the Last/Latest Value of a Category/Group Problem Statement: In the given data filter and show only the most recent row based on date/version etc. Data: I have taken data where we have ID and date along with status. And we would like to show the latest status. Solution: We will use a DAX measure for that. I have loaded the data to the power bi file and created the following DAX measure Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[ID] = Max(Data[ID])), Data[Date]) return CALCULATE(max(Data[Status]), filter((Data) , Data[Date] =_max)) Refer to the attached file.Learn Power BI: Tutorial for Beginners -Full Course
Learn Power BI Tutorial/training for Beginners, full/complete course- 11 hours. DAX, Power Query, and Power Platform overview, Load Data, Calculated Column, Measure, Visuals, Field Parameters, Bookmarks, Offset, Window, Index Functions, Time Intelligence, Interactions, Drill Through, Tooltip, Publish file, Pivot data, Unpivot data, Transpose data, Delete Blank and Duplicates. Full Video Link: https://www.youtube.com/watch?v=cN8AO3_vmlY Data: Covid 19: https://covid19.who.int/WHO-COVID-19-global-data.csv Get The data and Pbix files https://github.com/amitchandakpbi/powerbi/tree/main/Data%20for%20Learn%20Power%20BI%20Full%20Video Other File at GitHub https://github.com/amitchandakpbi/powerbi More Videos that you should watch How to create a login at app.powerbi.com: https://youtu.be/VW130u7u-G0 Incremental Refresh Pro; Premium Deployment pipeline - https://youtu.be/nIxTRdeCYSE On-Premise Gateway and configure it on service: https://youtu.be/lObMRofpbQ8 Dataflows and Dataset Design Pattern: https://youtu.be/zwhJ1hWPcrA Power BI- How to create Datamarts: https://youtu.be/8tskWsJTEpg Mastering Power BI: https://www.youtube.com/watch?v=wvsAzTqSDVg&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb Expertise Power BI: https://www.youtube.com/watch?v=59PUFuuCrbY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L Following are the 100+ Topics 1. Introduction 2. Basic Concepts 3. Download and Install Power BI 4. Quick Look at Power BI Desktop Dec 2022 5. Create Power Bi Login 6. Login/Sign in to Power B 7. Power BI EcoSystem 8. Power Bi Licenses 9. Power Bi Desktop Overview 10. Power BI Options & Settings 11. Covid Data Analysis 12. Download the data 13. Overview of Data 14. Load Data into Power BI 15. Column Tools 16. Data Model Property Pane 17. Check No Join issue 18. Single Vs Bi-Directional Join 19. Update Power BI Theme 20. Create Calculated Columns and Measures 21. Calculate with and Without filter 22. Expression Functions SUMX,MINX, MAXX 23. isfiltered, hasonevalue 24. Table Visual 25. Gradient Conditional Formatting 26. Rule based Conditional Formatting 27. Data Bars 28. URL Icon 29. Matrix Visual 30. Switch Values to Row 31. Stepped Layout off 32. Desc Sort on Matrix Column 33. Bar Visual 34. Concatenate Label off 35. Measure Based Conditional Formatting 36. Pie/Donut Visual 37. Line Visual 38. Area Visual 39. Tree Map Visual 40. Stacked Visual 41. Combo Visual 42. Small Multiples 43. Card Visual 44. Multi Row Card 45. Scatter Visual 46. Scatter Visual with quadrants conditional formatting 47. Map, Shape Map, Filled Map, Security Settings 48. Decomposition Tree 49. Q&A Visual 50. DAX Earlier 51. Date Table 52. Date Table FY Columns 53. DAX Search, Find 54. DAX containsstring, containsstringexact 55. DAX Left, Right, Mid 56. calculatetable 57. Summarize 58. Summarize Vs Groupby 59. Summarize in Measures 60. SummarizeColumns, Except, Distinct 61. Generateseries 62. Generate, Cross join 63. all, allselected, removefilters 64. allexcept 65. rankx, topn 66. dynamic topn with numeric parameters 67. Firstnonblankvalue, Lastnonblankvalue 68. Distinct count using values/summarize 69. Power BI Time Intelligence Setup 70. totalmtd, datesmtd, datesqtd, datesytd 71. Last datesmtd, Last datesqtd, Last datesytd 72. Trailing Day, Week, Month, Qtr, Year, Year Week 73. Previousmonth, previousquarter, previousyear 74. Week on Week, WTD 75. Half(custom period) Till Date, HoH,Period on Period 76. DAX offset 77. DAX isinscope 78. DAX window 79. DAX Index 80. Power BI Visual Interaction 81. Power BI Bookmarks 82. Field Parameter, Measure Slicer, Axis Slicer 83. Drill Through 84. Tooltip 85. Introduction to Power Query 86. Use First Row As a header, Remove Blank and Duplicates 87. Power Query UnPivot Data 88. Power Query Pivot Data 89. Power Query Transpose Data 90. Power Query Fill Up, Fill Down 91. Power Query Replace Values 92. Power Query Text Operations 93. Power Query Append Tables 94. Power Query Merge Tables 95. Power Query Custom Column 96. Power Query Extract Data 97. Column Quality, Profile and Distribution 98. Clean Power BI File 99. Publish Power BI File, Analyze on Power Bi Service 100. Create Report in Power BI Service 101. Power Bi Service Dashboard 102. Power Bi Service App 103. Power BI Dataflow 104. Power BI Live Connection 105. Power Bi Tenant SettingBetter Year Over Year Change
Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total, Better Average per Category, Better Weighted Average per Category, Better Filtered Value, Better Sales from New Customers, and Year to Date Total. This one tackle Year Over Year Change. Power BI's Year Over Year Change quick measure returns something like this: Value YoY% = VAR __PREV_YEAR = CALCULATE(SUM('Table'[Value]), DATEADD('Dates'[Date], -1, YEAR)) RETURN DIVIDE(SUM('Table'[Value]) - __PREV_YEAR, __PREV_YEAR) or this: Value YoY% 2 = IF( ISFILTERED('Dates'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_YEAR = CALCULATE(SUM('Table'[Value]), DATEADD('Dates'[Date].[Date], -1, YEAR)) RETURN DIVIDE(SUM('Table'[Value]) - __PREV_YEAR, __PREV_YEAR) ) Which may seem great until you try to use it with fiscal calendars and such. A better way: Better Year Over Year Change = VAR __Year = MAX('Table'[Year]) VAR __Curr = SUMX(FILTER(ALL('Table'),[Year] = __Year),[Value]) VAR __Prev = SUMX(FILTER(ALL('Table'),[Year] = __Year - 1),[Value]) RETURN DIVIDE(__Curr - __Prev, __Prev, 0) Watch the video! eyJrIjoiYWZlNDIzNjctMGUxZi00ZTU5LWI4MDgtYjI0MmRiYzQ4YjU3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
