Calculate the number of days between two dates excluding Weekends in powerbi using calculated column

0
votes

I am trying to calculate the number of working days between two dates in PowerBI excluding the weekends.

So, I have a table called as Calendar which has the date coming from 2000-2030 and another table which has the submitted date and today's date.

Where am I going wrong in this? My Calculated field Aging is showing wrong values and I cannot Identify why this is happening.

The Calculated Column and Tables

Tried with a measure and it says single value for Submitted_Date cannot be determined.

Aging2 = CALCULATE(SUM('Calendar'[IfWorkDay]),DATESBETWEEN('Calendar'[Date],(AgingReport[Submitted_Date]),(AgingReport[Today's Date])))
2
powerbi
Your formula seems to work fine as a calculated column. Are you trying to use it as a measure? – Alexis Olson
@AlexisOlson No, I just created a calculated column & then did summing when adding it to the table. – Siddharth Thanga Mariappan
"showing wrong values" - needs explanation and clearer example. – Mike Honey
@AlexisOlson :- Sounds so complex. Can you help me with this - stackoverflow.com/questions/55935908/… – Siddharth Thanga Mariappan

2 Answers

2
votes

As a Column:

_dc_Vol_TTR_BDays = // BusinessDays
SUMX(
    SELECTCOLUMNS(
        CALENDAR([_scCreatedDateYMD], [_scLastActiveDateYMD]),
        "Date", [_scCreatedDateYMD],
        "BDay", IF(WEEKDAY([Date],3) < 5, 1, 0)
    ),
    [BDay]
)
0
votes

The Problem was with the data type. They both needs to be on the same date type.