Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I have a rather specifc problem I've not been able to solve.
I would like to calculate the number of weekdays that sit between a date range, but the date range is dynamic.
I have the following code as a starting point:
Days Lost = VAR EndOfPeriod = MAX ('Date Table'[Date]) VAR StartOfPeriod = MIN ('Date Table'[Date]) RETURN SUMX(Absence, IFERROR( DATEDIFF( MAX(Absence[Begin Date],StartOfPeriod), MIN(Absence[Calculated End Date],EndOfPeriod) ,DAY),0) )
However, this is limited as it also includes weekends. As an added complication I would also like my final measure to treat the dates as inclusive, so if the begin date is equal to the end date then that should be counted as 1 rather than 0.
Any help you can provide would be greatly appreciated.
Many thanks.
Thanks for your reply, unfortunately this isn't quite what I'm looking for. Let me try and clarify a little,
I have a data set including the following columns : Begin Date & Calculated End Date during which a specific event was active.
I would like to produce a graph that has Quarters on the axis, for example Q1 2017, Q2 2017 and so on. For each of these quarters I would like the total number of days spent active, summed over every row of my table.
Say for example my data had two rows:
Start Date Calculated End Date
01/01/2018 01/09/2018
01/02/2018 01/09/2018
My graph with quarters on the axis would show the following total Q1: 107 This would be made up of the 65 weekdays within Q1 from row 1 and the 42 weekdays in Q1 from row 2. For Q2 the graph would show 130, made up of the 65 weekdays in Q2 from row 1, and the 65 weekdays in Q2 from row 2.
I hope this is a little clearer, thanks for your help.
Hi @Phil1,
Just use calendar table(DimDate) join your table with Dim_Date and drag weekdays columns hope this will resove you issue.ion
Please accept this solution if this works for you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
102 | |
78 | |
77 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |