Hi Alberto - This has been very helpful in getting me to a certain point. However, I would like to get your take on a next step. I'm trying to calculate the consecutive days (similar to the days with sales) between the last day an employee didn't work and the next day an employee didn't work. The desired outcome would be for a count of consecutive days between those two "blank" days (i.e. 1, 2, 3, 4, 5, etc.). This will then be used to determine how many times a colleague worked more than a certain number of days. I have tried using / modifying the code used in this video, but have had limited luck. There is an index, employee id, date, date worked and count of day worked in the dataset. I'm happy to share a test .pbix if you have any ideas. Thanks for all your help. I find your videos very informative!
Hmmm. I have two objections to this measure. 1. The VAR AllDates =ALL('Date'[Date]) should be ALLSELECTED('Date'[Date]) right? If not it will not take into account any dates filtered out by the user. 2. The calculation for the number of days without holes is preformed by subtracting dates. This to will not take into account any dates the user may wish to exclude from the calculation. The number of days without holes should be done by COUNTROWS if you as me :)
Thank you so much for the insightful video. Suppose, I want to calculate the consecutive working days for staff & report on each individual staff to ensure they don’t run into fatigue. How to make the above algorithm concept to work in such situation? Field are: Staff Name, Work date, No. of Hours worked.
Superb video just amazed with the dax. could we please have one video on sales projected for the month based on sales days and sales run rate. Many thanks
Hi Alberto, I have a question. Assuming the VERY FIRST date in your Date table, e.g., 2017-01-01, has sales, what will be the NearestHole value when the date in the report is also 2017-01-01? It appears under this situation, NearestHole will return blank. Therefore, Results= INT ( "2017-01-01" - blank ) . Right? So I guess we should add a IF condition to include such condition. Also, how about All dates in Date table have sales, which means we have no hole at all? DatesWithNoSales will be an empty table. NearestHole will return Blank. Right? Thank you.
Hi, would it be possible please to modify this code to look at all dates and dates from another table where people have taken personal leave so we count episodes of leave instead of episodes of Sales? (for Calculating Bradford Scores)
Hello Alberto. Sorry for my question not related to the topic, but I want to know your opinion, in this modern world and dax domination should I also start studying another concept as mdx and cubes or it will not have a bright future? I ask you because I know that you are familiar with the topic, thank you very much for your answer in advance.
MDX did not evolve in the last few years. It's still used by Excel also when querying Tabular models, or by other tools that use MDX as a query language. However, its adoption is certainly not raising today.
@@SQLBI it is not. you can even just read the title Consecutive-days-with-no-sales.zip (3.81 MB) NO SALES. this is a different video and blog on consecutive days WITH SALES. It is not the same file
Oh thank you! It was a wrong link that we now fixed - there was a coincidence with the name of another unplugged video with a similar name. Please, let us know if it works well now!
