Coming to this after reading your blog post on expanded tables and possibility of context transition, I can now appreciate your recognizing the need for an ALL('Date') in the filter. I hope to come to that state of recognition. Thank you for the depth of understanding you provide.
Excellent video. Each step explained very simply. I was scratching my head to calculate working days using DAX and couldn't find as easy solution as in this video! Thank you!! Keep posting such amazing solution videos for Power BI.
As with others not only did this give a good soloution to a minor problem but I have a similar issue but with Time and I will use similar principles to get the working time. Thank you!
Incredible insights and clear techniques! I have a questioning on mind, if i may: is it possible to combine working hours with this method? And, if it is possible, to generate a deadline on delivery based on a starting point (on this case, when the order is made)?
Another great video!! Looks like you guys are going to create more youtube contents going forward and changing the way articles are presented on SQLBI by adding video as well.
I just did this dax to create the workday column in a calendar. Is there a more effcient way? Workday = if( calculate( countrows(holidays), filter(holidays, holidays[Date]='date'[Date]) ) >= 1 || 'date'[Weekday] = "Sunday" || 'date'[Weekday] = "Saturday", "", "yes" )
Thank you for this video! I'm very new to powerBi and you are helping a lot. Is there a way to add holidays to the "IsWorkingDay = NOT WEEKDAY([Date] ) IN { 1,7 }" function? I read the related article from your article about workdays and powerpivot, but I had problems understanding it and applying it to this lesson to create the IsWorkingDay column with holidays.
You can check this article: www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/ The code in the sample is very advanced, but it handles holidays well.
Thank you this is really useful. Currently working through the Data Modelling course as well. At 4.37 you mention that you are adding ALL('Date') because CALCULATE is being used which is going to therefore create row evaluation, and you don't want context evaluation to affect this which is generated due to the relationship between Order Date and Date in the model. Am I correct in my summary?
Yes - technically the ALL('Date') removes the filter context generated by the context transition produced by calling CALCULATE in a row context (because we write a calculated column).
Hey, I am a little confused. In the blog, we have a filter ALL(Sales) and here we have it ALL('Date') I am very new and might be asking very basic questions but can you explain it a little which should be correct. I understand that CALCULATE adds the filter context and the calculated column has an in-built row context so there will be context transition, but I am unable to imagine its effect. How will the sales table, which is on the many side, filter the date table on the one side? Thanks
Up to 1-2 million rows in the table it makes sense, but if you have 10 million rows or more it is better to precalculate the value before importing data.
Hii Alberto, I thought of below dax code: Delivery working Days= VAR OrderDate = Sales[OrderDate] VAR DeliveryDate = Sales[DeliveryDate] VAR WorkingDays= COUNTROWS( FILTER( ALL(Date) -- no need of ALL, but still. ,AND(AND(Date[Date]>=OrderDate, Date[Date]
Another great video, thanks!! but this method not able to show the negative working days. (eg. looking for the overdue working days from start date to target date). is there any solution can show the negative value? appreciate and thanksss if anyone can provide some idea.
# of Working Days = CALCULATE( COUNTROWS('Detail'), DATESBETWEEN(Detail[Date], Detail[Start Date of Program0], Detail[Today] -1), Detail[Weekday?] = TRUE(), ALL('Detail') ) I used this code, but I am getting strange numbers. For instance, I get the answers, that someone was 5 days in a program, but it is giving me 12 Working days? I think it has something to do with the first item in the between dates. Detail[Date] is a copy of the Detail[Start Date of Program. I would appreciate any guidance.
I have blank rows in both my first date & last date. Also I have a condition for my second date where, if its blank then I need to consider another date column. on top of it, I need to remove the Saturdays and Sundays. can you suggest any solution for this.
I am calculating how many working days there are between our customer send the money and the due date of the invoice. If they paid on due date or later, that's ok. But what if they paid earlier - sometimes it happens. How do we calculate the negative difference? Thank you in advance!
I have to calculate from 2 datetime -for example - we have resolve datetime - created datetime and working hours 9 to 6 if greater than 6 then calculate in nxt day how can do in powerbi dax plz guide
Very nice presentation, but what if you want to create OTD measure, meaning result can be early delivery and so is minus? (this doesn't appear in the column) So end date is before required date....
Hello, I would like to count the days that were worked over 10 hours. The total of the hours is also calculated, but unfortunately I don't know how to count the days. CALCULATE(SUM(tbl_Daten[Hours]);OR(OR(OR(tbl_Daten[Legende]="A";tbl_Daten[Legende]="B");tbl_Daten[Legende]="C");tbl_Daten[Legende]="D"))
CALENDAR only creates a Date column, not the other columns to group by year/month/weekday. Auto Date/Time can do that for you but is way more expensive on other sides (there are other videos about that).
Yes, but in this case the calculated column provides better performance, doing the same calculation in a measure could slow down the performance depending on a number of other factors.
Hi All , im getting following error "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument." my code : PSCC = CALCULATE( COUNTROWS('BMT Calendar_Hierarchy'), DATESBETWEEN('BMT Calendar_Hierarchy',SUPPLY_ORDERS_APJ[Order Date],SUPPLY_ORDERS_APJ[Order Load Date]-1), 'BMT Calendar_Hierarchy'[ISweekday]=1, ALL('BMT Calendar_Hierarchy') ) can anyone helpme?
After a while looking on the Brazilian site about this subject, I found yours that helped me. Thanks
Coming to this after reading your blog post on expanded tables and possibility of context transition, I can now appreciate your recognizing the need for an ALL('Date') in the filter. I hope to come to that state of recognition. Thank you for the depth of understanding you provide.
Excellent video. Each step explained very simply. I was scratching my head to calculate working days using DAX and couldn't find as easy solution as in this video! Thank you!! Keep posting such amazing solution videos for Power BI.
Very nice. We need more this type of short video!
Congratulations on the excellent way to explain.
Simple, fast and practical.
Thank you very much for sharing your knowledge.
Be good friend.
As with others not only did this give a good soloution to a minor problem but I have a similar issue but with Time and I will use similar principles to get the working time. Thank you!
Exactly what i was searching for! Such great delivery of content...thank you.
Awesome video , thanks for doing this. You just gave me an idea 💡 that will help me to tackle a long standing issue with calendar and weekends.
Thank you! There is so much insight in one video. I enjoyed every second of it!
Damn man, you aged really well. Thanks for this video.
Thanks you for awesome video , could you explain to me why using countrows ?
Incredible insights and clear techniques!
I have a questioning on mind, if i may: is it possible to combine working hours with this method? And, if it is possible, to generate a deadline on delivery based on a starting point (on this case, when the order is made)?
Another great video!! Looks like you guys are going to create more youtube contents going forward and changing the way articles are presented on SQLBI by adding video as well.
How was Isworking date column added? I dint get that part
Thank you for this video it is very useful and very well explained.
Thank you very much, so appreciated.
I just did this dax to create the workday column in a calendar. Is there a more effcient way?
Workday =
if(
calculate(
countrows(holidays),
filter(holidays, holidays[Date]='date'[Date])
) >= 1
|| 'date'[Weekday] = "Sunday"
|| 'date'[Weekday] = "Saturday",
"",
"yes"
)
Thank you for this video! I'm very new to powerBi and you are helping a lot. Is there a way to add holidays to the "IsWorkingDay = NOT WEEKDAY([Date] ) IN { 1,7 }" function? I read the related article from your article about workdays and powerpivot, but I had problems understanding it and applying it to this lesson to create the IsWorkingDay column with holidays.
You can check this article: www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
The code in the sample is very advanced, but it handles holidays well.
excellent❤
amazing solution
Thank you this is really useful. Currently working through the Data Modelling course as well. At 4.37 you mention that you are adding ALL('Date') because CALCULATE is being used which is going to therefore create row evaluation, and you don't want context evaluation to affect this which is generated due to the relationship between Order Date and Date in the model. Am I correct in my summary?
Yes - technically the ALL('Date') removes the filter context generated by the context transition produced by calling CALCULATE in a row context (because we write a calculated column).
Hey, I am a little confused. In the blog, we have a filter ALL(Sales) and here we have it ALL('Date') I am very new and might be asking very basic questions but can you explain it a little which should be correct.
I understand that CALCULATE adds the filter context and the calculated column has an in-built row context so there will be context transition, but I am unable to imagine its effect. How will the sales table, which is on the many side, filter the date table on the one side?
Thanks
Amazing solution. I was scratching my head on for looping in power bi. :(
Alberto. How the same task can be done using power query sequence? Possibly using multiple calendars... Ie a calendar for each row...? Thank you.
Thank you.. Calculated column on a large sales table, is it really a choice? Or is it just for the sake of video??
Up to 1-2 million rows in the table it makes sense, but if you have 10 million rows or more it is better to precalculate the value before importing data.
@@SQLBI Yes, Thank you 👍
Hii Alberto,
I thought of below dax code:
Delivery working Days=
VAR OrderDate = Sales[OrderDate]
VAR DeliveryDate = Sales[DeliveryDate]
VAR WorkingDays=
COUNTROWS(
FILTER(
ALL(Date) -- no need of ALL, but still.
,AND(AND(Date[Date]>=OrderDate, Date[Date]
Thanks! Sir
Another great video, thanks!! but this method not able to show the negative working days. (eg. looking for the overdue working days from start date to target date). is there any solution can show the negative value? appreciate and thanksss if anyone can provide some idea.
# of Working Days =
CALCULATE(
COUNTROWS('Detail'),
DATESBETWEEN(Detail[Date], Detail[Start Date of Program0], Detail[Today] -1),
Detail[Weekday?] = TRUE(),
ALL('Detail')
)
I used this code, but I am getting strange numbers. For instance, I get the answers, that someone was 5 days in a program, but it is giving me 12 Working days? I think it has something to do with the first item in the between dates. Detail[Date] is a copy of the Detail[Start Date of Program. I would appreciate any guidance.
I have blank rows in both my first date & last date. Also I have a condition for my second date where, if its blank then I need to consider another date column. on top of it, I need to remove the Saturdays and Sundays. can you suggest any solution for this.
I am calculating how many working days there are between our customer send the money and the due date of the invoice. If they paid on due date or later, that's ok. But what if they paid earlier - sometimes it happens. How do we calculate the negative difference? Thank you in advance!
thanks
I have to calculate from 2 datetime -for example - we have resolve datetime - created datetime and working hours 9 to 6 if greater than 6 then calculate in nxt day how can do in powerbi dax plz guide
Very nice presentation, but what if you want to create OTD measure, meaning result can be early delivery and so is minus? (this doesn't appear in the column) So end date is before required date....
The calculation is the same, you might just want to create another column to split positive and negative numbers in two different groups.
@@SQLBI thank you, in the meantime it works with use of IF.
@@SQLBI How? datesbetween only shows the diff. Not as integers
In date table why we are taken only weekly one date. Any reason ? Here can i take continuous dates ?
How do we do that without creating a calculated column?
Unfortunately, It did not work for me. It keeps returning nonsensical values. Clearly I am missing something or my datasets are messed up.
Hello,
I would like to count the days that were worked over 10 hours. The total of the hours is also calculated, but unfortunately I don't know how to count the days.
CALCULATE(SUM(tbl_Daten[Hours]);OR(OR(OR(tbl_Daten[Legende]="A";tbl_Daten[Legende]="B");tbl_Daten[Legende]="C");tbl_Daten[Legende]="D"))
Could have used the "CALENDAR" function that's already available in DAX with out creating your own 'Date' table.
CALENDAR only creates a Date column, not the other columns to group by year/month/weekday. Auto Date/Time can do that for you but is way more expensive on other sides (there are other videos about that).
@@SQLBI which is fine if all you are doing is calculating age doesn't affect the outcome for splitting volumes by year/month/day.
Can we just do the calculation with a measure instead of creating a calculated column?
Yes, but in this case the calculated column provides better performance, doing the same calculation in a measure could slow down the performance depending on a number of other factors.
Hi All , im getting following error "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument."
my code :
PSCC =
CALCULATE(
COUNTROWS('BMT Calendar_Hierarchy'),
DATESBETWEEN('BMT Calendar_Hierarchy',SUPPLY_ORDERS_APJ[Order Date],SUPPLY_ORDERS_APJ[Order Load Date]-1),
'BMT Calendar_Hierarchy'[ISweekday]=1,
ALL('BMT Calendar_Hierarchy')
)
can anyone helpme?
The first argument of DATESBETWEEN must be a column reference, not a table name, just as written in the error message.
Thanks