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.
Solved! Go to Solution.
Please see the attached file
Hey, Im struggling calculating average of a column which contains difference between two dates. When calculating in excel the results is ok, but not in power bi.
Steps I´ve followed (as an exacmple):
1; If (B1=blank():blank():(B1-A1))
2. New measure: average column
Any suggestion?
Hi,
Hey,
Let me explain myself a little better. In excel I have the following:
Date Created Date Assigned Date Finished
Some of them are blank. I want to have three measures:
1, Days between Date created and date finished. After that, I want to calculate the average excluding blanks
2. Days between date created and date assigned. After that, I want to calculate the average excluding blanks
3. Days between date assigned and date finished. After that, I want to calculate the average excluing blanks.
The "new column" im including in power bi is just as simple as: if(date assigned=blank();blank();(date assigned-date created)
The "new measure" = average(new column)
The results in excel are accurate (average aprox 1.2) and in power bi are over 30 days, which doesnt make sense...
Any ideas? Its driving my crazy 😞
That's strange. Share the link from where i can download your PBI file.
Hi @usman96
You can use this calculated column....assuming you have Startdate and Enddate columns
Count Business Days = VAR MyDates = ADDCOLUMNS ( GENERATESERIES ( TableName[StartDate] + 1, TableName[ EndDate] ), "Day", WEEKDAY ( [Value], 2 ) ) RETURN COUNTROWS ( FILTER ( mydates, [Day] <= 6 ) )
what is "value" in your formula
It is the temporary Column created by the temporary table
GENERATESERIES ( TableName[StartDate] + 1, TableName[ EndDate] ),
for each row of your table
Please see the attached file here
I only have date column , not starting date and ending date
Then what difference do you want to count if you have a single date?
You can copy paste some sample data here with expected results
For example
StartDate | EndDate | Count Business Days |
Monday, January 1, 2018 | Saturday, January 13, 2018 | 11 |
Tuesday, January 9, 2018 | Thursday, January 18, 2018 | 8 |
Thursday, January 18, 2018 | Wednesday, January 31, 2018 | 11 |
I have a single column like this and i want to find how many days between these two dates (exlcuding sunday)
(as u can see , one is smaller date and one is larger )
Do you have only 2 dates in your Column?
yes for now , but as enter more data .............. the table structure should be like in Ascending order (Smaller dates to larger dates)
Suppose you have a table like this
Date |
Monday, January 1, 2018 |
Saturday, January 13, 2018 |
Sunday, January 21, 2018 |
Saturday, February 3, 2018 |
You can add a calculated column to get the difference from next date using this formula
Difference from Next date = VAR NextDate = CALCULATE ( MIN ( Table1[Date] ), FILTER ( ALL ( Table1 ), Table1[Date] > EARLIER ( Table1[Date] ) ) ) VAR Next_date = IF ( ISBLANK ( NextDate ), Table1[Date] + 1, NextDate ) VAR MyDates = ADDCOLUMNS ( GENERATESERIES ( Table1[Date] + 1, Next_Date ), "Day", WEEKDAY ( [Value], 2 ) ) RETURN COUNTROWS ( FILTER ( mydates, [Day] <= 6 ) )
Please see the attached file
Thanks 🙂
what should i do ?
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |