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 am calculating the difference in seconds between two dates in my data model. For calculation purpose I have converted two of my date columns from fact to measure using the below :
Solved! Go to Solution.
Hi @Anonymous ,
Or do like this.
1. Cretae 4 calculated columns.
Weekday_sub = WEEKDAY([submitted], 2)
Weekday_res = WEEKDAY([resolved], 2)
Weeknum_sub = WEEKNUM( [submitted], 2 )
Weeknum_res = WEEKNUM( [resolved], 2 )
2. Create a measure.
Measure =
CALCULATE(
DATEDIFF(
MAX('Fact'[submitted]), MAX( 'Fact'[resolved]),
DAY
),
FILTER(
'Fact',
( 'Fact'[Weekday_sub] in {6, 7} || 'Fact'[Weekday_res] in {6, 7} ) &&
( 'Fact'[Weeknum_sub] = 'Fact'[Weeknum_res] ) &&
( ( 'Fact'[Weekday_res] in {6, 7} && 'Fact'[Weekday_sub] in {1, 2, 3, 4, 5} ) || ( 'Fact'[Weekday_sub] in {6, 7} && 'Fact'[Weekday_res] in {1, 2, 3, 4, 5}) )
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Take a look at the blue card visual in this file https://www.dropbox.com/s/ww9063que0bcvrn/datediff%20in%20second.pbix?dl=0
If I understood your requirement correctly, this should hopefully help
@Pascal_KTeam thanks but this does not solve my purpose. I have to calculate the duration in seconds based on certain conditions that I have mentioned in my earlier message.
@Anonymous , date dim is not joined to dates and date diff has no role there. so it is not working
Refer to this file how to get working day diff
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
Blog has instructions : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
Maybe I was not clear with my requirements so, My fact table has two date columns submitted and resolved_at now I want to calculate the number of seconds between these two dates based on the conditions below
a. submitted and resolved on weekends
b.submitted and resolved in the same week
c. submitted on weekends and resolved on a weekday
d.submitted on a weekday and resolved on weekend
I have a date dimension that is connected to the fact on submitted and resolved_at dates.
thanks
Hi @Anonymous ,
Or do like this.
1. Cretae 4 calculated columns.
Weekday_sub = WEEKDAY([submitted], 2)
Weekday_res = WEEKDAY([resolved], 2)
Weeknum_sub = WEEKNUM( [submitted], 2 )
Weeknum_res = WEEKNUM( [resolved], 2 )
2. Create a measure.
Measure =
CALCULATE(
DATEDIFF(
MAX('Fact'[submitted]), MAX( 'Fact'[resolved]),
DAY
),
FILTER(
'Fact',
( 'Fact'[Weekday_sub] in {6, 7} || 'Fact'[Weekday_res] in {6, 7} ) &&
( 'Fact'[Weeknum_sub] = 'Fact'[Weeknum_res] ) &&
( ( 'Fact'[Weekday_res] in {6, 7} && 'Fact'[Weekday_sub] in {1, 2, 3, 4, 5} ) || ( 'Fact'[Weekday_sub] in {6, 7} && 'Fact'[Weekday_res] in {1, 2, 3, 4, 5}) )
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
instead of
Measure = CALCULATE(DATEDIFF([Resolved_Date],[Created],DAY),
try
Measure = CALCULATE(DATEDIFF([Resolved_Date],[Created],SECOND),
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |