I can’t express enough how good this video is. The idea with WDN in Date table and -NOT(‘Date’[Working Day] in the code was excellent. Presenting 2 solutions with performance comparison is always second to none approach. Thank you! 👏
Hello, i'm from Brazil, i was looking for a answer for my problem during a whole week, and i found u, u resolve my problem in 30 minutes, i can't express how gratefull i am, u are amazing, thank you
Thank you Alberto for delivering happiness 😃 I enjoy watching your videos more than listening to my favorite music. In this video, when reached to part where you involved the Boolean expression in the calculation, I was about to jump out of joy. It was just amazing! I was thinking that it might be easier to multiply with the Boolean condition instead of subtracting “NOT”. This would return all the non-working days to zero.
This is an amazing solution! Thanks for sharing it. I have developed further some part of it but your idea is still the fundamental of my solution. Thanks
I feel so lucky that I chanced upon this video, thank you so much! However, after correctly ranking the working days in the calendar table, I am now trying to create a measure that would calculate the target date after "N" working days based on the current date and the "N" value or the number of working days to be incremented is dynamically calculated from another measured calculation. I would be very grateful for any help.
Hi Alberto, thank you so much for the explanation. It’s very clear and quite easy to follow. Just wondering, what if we want to exclude the holiday list from this case? So the idea is I wanna only add to the next 10 working days and also exclude the holidays (it can be there is a holiday in the weekdays, right?). I hope you read my comment and kindly give me some tips. Thank you so much in advance
Hi Alberto, This is a great first step to an YoY comparison I am trying to build, I just have a follow up step to work on. I am trying to create the same count for working days; however, I need the count to restart every year. My ultimate goal is to compare sales numbers YoY based on the number of working days. For example, I want to compare the number of sales we have this year at working day 54 versus the sales we had last year on working day 54. What would be the easiest way to restart the count every year? Thanks much!
hi thanks for the video. somehow both solutions are not working for me. i created dummy date with date column having the values: 9/10/2023 10:10:10, 9/10/2023 11:11:11, 10/10/2023 10:10:10, 10/10/2023 11:11:11, 10/10/2023 12:12:12 i used your first solution, and it returned the same result for all rows, which is 10/10/2023 12:12:12, and with the last row (the latest datetime) blank then i used your second solution and got the result as "1" for all rows
If you work with different holiday calendars like in logistics is often the case, you are out of luck and you need to go with a more complex form of the first solution, right?
I believe the second solution will still work, you will just have many columns in your date table for the different holiday scenarios and replicate the formula multiple times. Just ensure you have a really intuitive naming convention so you don't get confused.
Hi I have a query. I need to write a dax query to calculate salary per day I my data model I have Working Days column which contain all the working days date example 1 January 2023 2 January 2023 till 31 January 2023. I have a column name Attendance status where I have two status Present and Absent I have a column name Salary where I have a monthly salary on 5th of each month from Working Days column. Please Please help me for this problem
In 23:05 could we just modify the formula so that it returns blank for non working days? Then only one row will contain the value and will be a working day.
This is a good idea. The value zero could be OK as well. I implemented this column in my SQL Server-based date table. But I struggle to implement the column WorkdayNumber in my DAX date table.
Hi Sir could you please help to find the working days to a date but each month should have their ranking the video has helped me a lot but i have only challenge to get working day number for each month
It's Done i just change one thing in the entire formula VAR WorkingDates = CALCULATETABLE( VALUES(Calender[Date]), ALLEXCEPT(Calender,Calender[Month]), Calender[Working Day] = TRUE )
If we add the column from date table to the fact table with RELATED function, can we create an inactive relationship with it and the date column or vertipaq will explode?
Sir, please create a video on it "How to get Facebook page, LinkedIn Profile real time data into Power BI and how to analyze it?" please sir it's my humble request to you. God bless you Thanks and Regards Muhammad Ali
I can’t express enough how good this video is. The idea with WDN in Date table and -NOT(‘Date’[Working Day] in the code was excellent.
Presenting 2 solutions with performance comparison is always second to none approach. Thank you! 👏
Hello, i'm from Brazil, i was looking for a answer for my problem during a whole week, and i found u, u resolve my problem in 30 minutes, i can't express how gratefull i am, u are amazing, thank you
I just don't undertand the second example, my rankx returns just 1, so i keep the first example to my problem.
Thank you Alberto for delivering happiness 😃 I enjoy watching your videos more than listening to my favorite music. In this video, when reached to part where you involved the Boolean expression in the calculation, I was about to jump out of joy. It was just amazing!
I was thinking that it might be easier to multiply with the Boolean condition instead of subtracting “NOT”. This would return all the non-working days to zero.
This is an amazing solution! Thanks for sharing it. I have developed further some part of it but your idea is still the fundamental of my solution. Thanks
This is very good Alberto! DAX at a very high level, really like how you think and come with an 'easy' but oh so good solution.
I would like to get this DAX level at some point. Nice video!
I feel so lucky that I chanced upon this video, thank you so much! However, after correctly ranking the working days in the calendar table, I am now trying to create a measure that would calculate the target date after "N" working days based on the current date and the "N" value or the number of working days to be incremented is dynamically calculated from another measured calculation. I would be very grateful for any help.
Great work!
Awesome! Any idea why date formating is different in moment 11:29? Do we Need ALL in EVALUATE ALL()
Hi Alberto, thank you so much for the explanation. It’s very clear and quite easy to follow. Just wondering, what if we want to exclude the holiday list from this case? So the idea is I wanna only add to the next 10 working days and also exclude the holidays (it can be there is a holiday in the weekdays, right?). I hope you read my comment and kindly give me some tips. Thank you so much in advance
Hi Alberto,
This is a great first step to an YoY comparison I am trying to build, I just have a follow up step to work on.
I am trying to create the same count for working days; however, I need the count to restart every year.
My ultimate goal is to compare sales numbers YoY based on the number of working days. For example, I want to compare the number of sales we have this year at working day 54 versus the sales we had last year on working day 54.
What would be the easiest way to restart the count every year?
Thanks much!
Is "further ado" your English Of The Week? Nice.
Kind of. I recently learned it while watching "The Crown", liked it and used it :)
hi thanks for the video. somehow both solutions are not working for me. i created dummy date with date column having the values: 9/10/2023 10:10:10, 9/10/2023 11:11:11, 10/10/2023 10:10:10, 10/10/2023 11:11:11, 10/10/2023 12:12:12
i used your first solution, and it returned the same result for all rows, which is 10/10/2023 12:12:12, and with the last row (the latest datetime) blank
then i used your second solution and got the result as "1" for all rows
If you work with different holiday calendars like in logistics is often the case, you are out of luck and you need to go with a more complex form of the first solution, right?
I believe the second solution will still work, you will just have many columns in your date table for the different holiday scenarios and replicate the formula multiple times. Just ensure you have a really intuitive naming convention so you don't get confused.
You can get this logic pre-calculated including holidays into date table as working day filter as well.
Hi I have a query. I need to write a dax query to calculate salary per day I my data model I have Working Days column which contain all the working days date example 1 January 2023 2 January 2023 till 31 January 2023. I have a column name Attendance status where I have two status Present and Absent I have a column name Salary where I have a monthly salary on 5th of each month from Working Days column. Please Please help me for this problem
Wow learned so much especially @ 20:05
I always wonder how the RANKX function is so optimized and fast, do you have something I can read?
Read this article: www.sqlbi.com/articles/how-to-compute-index-numbers-at-top-speed/
In 23:05 could we just modify the formula so that it returns blank for non working days? Then only one row will contain the value and will be a working day.
The reason that won't be a good idea because you want to be able to calculate the next Nth working day for non working days as well.
This is a good idea. The value zero could be OK as well.
I implemented this column in my SQL Server-based date table. But I struggle to implement the column WorkdayNumber in my DAX date table.
Right. You need the WDN for non-working days, and the price to pay is not that high, just another condition in LOOKUPVALUE.
@@SQLBI oh, indeed you absolutely right
Hi Sir could you please help to find the working days to a date but each month should have their ranking the video has helped me a lot but i have only challenge to get working day number for each month
It's Done i just change one thing in the entire formula VAR WorkingDates =
CALCULATETABLE(
VALUES(Calender[Date]),
ALLEXCEPT(Calender,Calender[Month]),
Calender[Working Day] = TRUE
)
If we add the column from date table to the fact table with RELATED function, can we create an inactive relationship with it and the date column or vertipaq will explode?
It depends on the size of the fact table, how much memory you have, and how much time you can wait.
So you also teach sql?
Sir, please create a video on it "How to get Facebook page, LinkedIn Profile real time data into Power BI and how to analyze it?" please sir it's my humble request to you.
God bless you
Thanks and Regards
Muhammad Ali