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!
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.
Hello - Great video! How might you do this for a monthly scale? This is working perfectly for days, but would be brilliant at a monthly e.g., Jan: 1 Feb: 2 and so on. Thanks!
You should create a consecutive month number in Date and count the unique values there. For examples of Date tables like that look at www.daxpatterns.com/time-patterns/
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 :)
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!
This follow-up to Unplug #18 is even greater in execution and delivery. Simply Excellent!!!
The way you unbundled a quite complicated measure in a simple way is superlative.
''the algorithm is quite simple, but it's worth it to see how it works!" ;D
Thanks for this demo!
From the Result , how can we remove public holidays and weekends if we want to ?
You run out superlatives watching Sqlbi, another brilliant video gents
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!
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.
I am also trying to find a solution to this problem. Does anyone know how I can display the current consecutive days worked for all employees?
Hello - Great video! How might you do this for a monthly scale?
This is working perfectly for days, but would be brilliant at a monthly e.g., Jan: 1 Feb: 2 and so on.
Thanks!
You should create a consecutive month number in Date and count the unique values there. For examples of Date tables like that look at www.daxpatterns.com/time-patterns/
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 :)
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)
How would this work for consecutive month ends. Not a text month column but the month end 1/31/2013
I WANT TO SHOW CONSECUTIVE INCREASE IN 5 DAY OR 10 DAY IN POWER BI HOW CAN I DO THESE
Hello SQLBI
Have you listed any of your courses in udemy, share the link
You can find our free and commercial courses here: www.sqlbi.com/training
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.
brilliant as always
The file on your web site links to wrong file. Can u please fix
The file seems correct.
@@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!
Brilliant!
Amazing!
Brilliant