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.
Hi All,
I would like to created a calculated column where it calculates date difference between two columns and return if it falls under 0-24 hrs or 24-48 hrs or 48-72 hrs or above 72hrs. Excluding only Sundays.
Table:
Start Date | Fruit Name | Type | Price Sold | End Date |
01-31-2020 | Orange | Type-2 | 7 | 02-01-2020 |
01-31-2020 | Apple | Type-3 | 8 | 02-01-2020 |
01-31-2020 | Grapes | Type-1 | 2 | 02-03-2020 |
01-31-2020 | Orange | Type-1 | 5 | 02-03-2020 |
01-31-2020 | Orange | Type-3 | 10 | 02-03-2020 |
01-31-2020 | Apple | Type-1 | 3 | 02-01-2020 |
01-31-2020 | Grapes | Type-2 | 4 | 02-01-2020 |
01-31-2020 | Grapes | Type-3 | 6 | 02-03-2020 |
01-31-2020 | Orange | Type-1 | 5 | 01-31-2020 |
01-31-2020 | Orange | Type-2 | 7 | 01-31-2020 |
01-31-2020 | Apple | Type-3 | 8 | 01-31-2020 |
01-31-2020 | Grapes | Type-1 | 2 | 01-31-2020 |
01-30-2020 | Orange | Type-1 | 5 | 02-03-2020 |
01-30-2020 | Orange | Type-3 | 10 | 02-03-2020 |
01-30-2020 | Apple | Type-1 | 3 | 02-03-2020 |
01-30-2020 | Grapes | Type-2 | 4 | 02-01-2020 |
01-30-2020 | Grapes | Type-3 | 6 | 02-01-2020 |
01-30-2020 | Orange | Type-1 | 5 | 02-01-2020 |
Expected Output:
Start Date | Fruit Name | Type | Price Sold | End Date | Expected Output |
01-31-2020 | Orange | Type-2 | 7 | 02-01-2020 | 24 to 48 Hrs |
01-31-2020 | Apple | Type-3 | 8 | 02-01-2020 | 24 to 48 Hrs |
01-31-2020 | Grapes | Type-1 | 2 | 02-03-2020 | 48 to 72 Hrs |
01-31-2020 | Orange | Type-1 | 5 | 02-03-2020 | 48 to 72 Hrs |
01-31-2020 | Orange | Type-3 | 10 | 02-03-2020 | 48 to 72 Hrs |
01-31-2020 | Apple | Type-1 | 3 | 02-01-2020 | 24 to 48 Hrs |
01-31-2020 | Grapes | Type-2 | 4 | 02-01-2020 | 24 to 48 Hrs |
01-31-2020 | Grapes | Type-3 | 6 | 02-03-2020 | 48 to 72 Hrs |
01-31-2020 | Orange | Type-1 | 5 | 01-31-2020 | 0 to 24 Hrs |
01-31-2020 | Orange | Type-2 | 7 | 01-31-2020 | 0 to 24 Hrs |
01-31-2020 | Apple | Type-3 | 8 | 01-31-2020 | 0 to 24 Hrs |
01-31-2020 | Grapes | Type-1 | 2 | 01-31-2020 | 0 to 24 Hrs |
01-30-2020 | Orange | Type-1 | 5 | 02-03-2020 | More than 72 Hrs |
01-30-2020 | Orange | Type-3 | 10 | 02-03-2020 | More than 72 Hrs |
01-30-2020 | Apple | Type-1 | 3 | 02-03-2020 | More than 72 Hrs |
01-30-2020 | Grapes | Type-2 | 4 | 02-01-2020 | 48 to 72 Hrs |
01-30-2020 | Grapes | Type-3 | 6 | 02-01-2020 | 48 to 72 Hrs |
01-30-2020 | Orange | Type-1 | 5 | 02-01-2020 | 48 to 72 Hrs |
Solved! Go to Solution.
Hi @labuser1235 ,
In your scenario, we should create a date table like that.
date = ADDCOLUMNS(CALENDARAUTO(),"Sunday",IF(WEEKDAY([Date],2) = 7,1,0))
Then we can create a calculated column in our fact table as below.
Column =
VAR workingdays =
CALCULATE (
COUNTROWS ( 'date' ),
FILTER (
'date',
'date'[Date] > 'Table'[Start Date]
&& 'date'[Date] <= 'Table'[End Date]
&& 'date'[Sunday] <> 1
)
)
RETURN
SWITCH (
TRUE (),
workingdays = 1, "0 to 24 Hrs",
workingdays = 2, "24 to 48 Hrs",
workingdays = 3, "48 to 72 Hrs",
"More that 62 Hrs"
)
For more details, please check the pbix as attached.
Hi @labuser1235 ,
In your scenario, we should create a date table like that.
date = ADDCOLUMNS(CALENDARAUTO(),"Sunday",IF(WEEKDAY([Date],2) = 7,1,0))
Then we can create a calculated column in our fact table as below.
Column =
VAR workingdays =
CALCULATE (
COUNTROWS ( 'date' ),
FILTER (
'date',
'date'[Date] > 'Table'[Start Date]
&& 'date'[Date] <= 'Table'[End Date]
&& 'date'[Sunday] <> 1
)
)
RETURN
SWITCH (
TRUE (),
workingdays = 1, "0 to 24 Hrs",
workingdays = 2, "24 to 48 Hrs",
workingdays = 3, "48 to 72 Hrs",
"More that 62 Hrs"
)
For more details, please check the pbix as attached.
I've just realised you want Sundays excluded also. Do you have a Date Dimensions table in use?
If so, you can add an additional column, IsWeekDay, using the SWITCH function;
IsWeekDay =
SWITCH (
WEEKDAY ( [Date] ),
1, 0,
1
)
This basically says if weekday is 1 (Sunday) then 0 (false), else 1 (True), which can be referenced in a second measure;
=
CALCULATE (
SUM ( DateTable[IsWorkday] ),
DATESBETWEEN (
DateTable[Date],
'Source'[StartDate],
'Source'[EndDate]
)
)
Finally, this can be referenced in the original If formula or a new SWITCH to convert the integers into your string for binning.
I hope that helps a little more for you.
Hi @labuser1235, sounds like an if formula is needed here.
https://docs.microsoft.com/en-us/dax/if-function-dax
=if ( datediff ([StartDate],[EndDate],HOUR) < 24, "Less Than 24 Hours",
if (datediff ([StartDate],[EndDate],HOUR)>=24 and datediff ([StartDate],[EndDate],HOUR)<48,"24-48 Hours",
if (datediff ([StartDate],[EndDate],HOUR)>=48 and datediff ([StartDate],[EndDate],HOUR)<72,"48-72 Hours","More Than 72 Hours")
Alternatively you could change the datediff to days, which would return whole integers (0,1,2,3 etc) that could be used in a SWITCH function. you can read more, here;
https://docs.microsoft.com/en-us/dax/switch-function-dax
Does that help?
@javirmerino Thank you so much for your quick response. I want it to ignore Sundays. How can we filter that could you please re-write the formula in that way.
Create a date table and use that get days and use that to get your logic.
If you have date table. the You can have new column in your table.
days diff = countx(filter(Date,date[date]>=table[Start_date] && date[date]<=table[Start_date]),If(weekday(date[date])=1,0,1))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
Hi @amitchandak ,
I have a Dimdate table where weekdays column is available as "Sunday,Monday,Tuesday etc..,".
And both the table are related via Table1 Start date to Dimdate Table date.
However, I want the output column to be as 0 to 24Hrs, 24 to 48Hrs, 48 to 72Hrs, More than 72Hrs.
days diff = countx(filter(Date,date[date]>=table[Start_date] && date[date]<=table[Start_date]),If(weekday(date[date])=1,0,1))
How can we achieve using your formula.
On top of that, you can write another column or in same column taking that as var
Switch(true(),
table[days diff] <=1 , "0 to 24Hrs",
table[days diff] >1 && table[days diff] <=2 , "24 to 48Hrs",
table[days diff] >2 && table[days diff] <=3, "48 to 72Hrs",
"More than 72Hrs")
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |