PDURATION
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
Understanding the PDURATION Function
The PDURATION function is used to calculate the duration between two dates based on a specific workday calendar. This function takes three arguments:
– start_date: The start date of the period
– end_date: The end date of the period
– weekend: The number that represents the days of the week that are considered weekends
The weekend argument is optional, and the default value is 1, which represents Saturday and Sunday as weekends. If you want to include other weekdays as weekends, you can specify them as a binary number. For example, if you want to include Friday and Saturday as weekends, you would use 65 as the weekend argument (1 + 64).
The output of the PDURATION function is a decimal value that represents the number of working days between the start date and end date.
Using the PDURATION Function in Power BI
To use the PDURATION function in Power BI, you will need to create a measure that references the function. Here is an example of a measure that calculates the duration between two dates based on a workday calendar with Saturday and Sunday as weekends:
Duration = PDURATION('Calendar'[Start Date],'Calendar'[End Date])
In this example, ‘Calendar'[Start Date] and ‘Calendar'[End Date] are the columns that contain the start and end dates, respectively. The measure is named “Duration” and references the PDURATION function with the default weekend argument.
Once you have created the measure, you can add it to a visual in your report to display the duration between two dates. For example, you might create a table that shows the start and end dates for a project, along with the duration in working days:
| Project Name | Start Date | End Date | Duration |
| ————|————|———-|———-|
| Project A | 1/1/2021 | 1/15/2021| 10.0 |
| Project B | 2/1/2021 | 2/15/2021| 8.0 |
In this table, the “Duration” column is calculated using the PDURATION function, and shows the number of working days between the start and end dates.
Customizing the Weekend Argument
If you need to include other weekdays as weekends, you can customize the weekend argument in the PDURATION function. For example, if you want to include Friday and Saturday as weekends, you would use the following formula:
Duration = PDURATION('Calendar'[Start Date],'Calendar'[End Date], 65)
In this formula, 65 is the binary representation of Friday and Saturday (1 + 64).
You can also use a column in your dataset to specify the weekend argument, rather than hard-coding it into the measure. For example, you might have a column that specifies the workday calendar for each project, and use that column to calculate the duration:
Duration = PDURATION('Calendar'[Start Date],'Calendar'[End Date], 'Projects'[Weekend])
In this formula, ‘Projects'[Weekend] is the column that contains the weekend argument for each project.
The PDURATION function is a powerful tool for calculating the duration between two dates based on a specific workday calendar. By using this function in Power BI, you can create more accurate and useful metrics for your reports and dashboards. Whether you need to include custom weekends or simply calculate working days between two dates, the PDURATION function can help you achieve your goals in Power BI.
