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.
I am trying to do something that is very easy in Excel but haven't found the correct syntax in Power BI yet.
I need to create a measure that calcualtes the number of days between two dates. I am not looking to do anything special like network days just plain old calendar days between dates in Column A to dates in Column B. What is the simpliest way to calulate this in Power BI?
Thanks.
Judie
Solved! Go to Solution.
@jb123 That would be the DATEDIFF function => DATEDIFF ( <star_date>, <end_date>, <interval> )
An error is returned if start_date is larger than end_date.
To avoid this error use this formula in a Calculated Column
Days Column = SWITCH ( TRUE (), 'Table'[start_date] < 'Table'[end_date], DATEDIFF ( 'Table'[start_date], 'Table'[end_date], DAY ), 'Table'[start_date] > 'Table'[end_date], DATEDIFF ( 'Table'[end_date], 'Table'[start_date], DAY ) * -1, 0 )
You multiply * -1 to get negative numbers where the event ended before it started (implying time travel )
Here's the result...
Hope this helps!
Hello,
My table has no start and end date. In my data table has got one date column. When i get table it seems like that;
ID | Visited Country | Date |
12345 | USA | 9.May |
12345 | USA | 10.May |
12345 | USA | 11.May |
12345 | USA | 12.May |
54321 | UK | 3.May |
54321 | UK | 4.May |
54321 | UK | 5.May |
54321 | UK | 6.May |
But instead of which days i need to show;
ID | Visited Country | Calculated Days |
12345 | USA | 4 |
54321 | UK | 6 |
Thank You!
Hi Sean
Could not quite figure it out until I discovered your post - works a treat. Thanks 🙂
That worked! Thank you
Hi @jb123,
Please mark the right reply as answer, so the people who have similar issue can find solution easily and clearly. Thanks a lot.
Best Regards,
Angelia
I am not sure how it's working for you but when applied exactly the same formula for a new measure it does not allow me to put the column names like 'start date' and 'end date'. It only accept the formula or measure already created...Any suggestions?
The above formula is for a Calculated Column
When you try to create a Measure
Intellisense does not allow you to select a column because you need an aggregator first such as MIN or MAX
So here is the Measure formula
Days Measure = SWITCH ( TRUE (), MIN('Projects'[start_date]) < MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[start_date]), MIN('Projects'[end_date]), DAY ), MIN('Projects'[start_date]) > MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[end_date]), MIN('Projects'[start_date]), DAY )* -1 )
And here's the result...
Hope this helps!
First, thanks for this.. as close as I've come to solving the problem. However, it doesn't quite work as expected with my data and it may be that it has something to do with the two tables ('basic') and ('statuschanges') involved. The are related, but when I use this formula, the "Days Measure" column returns a constant 365 for all records:
Days Measure =
SWITCH (
TRUE (),
MIN('basic'[submissiondate]) < MIN('statuschanges'[statusdate]), DATEDIFF ( MIN('basic'[submissiondate]), MIN('statuschanges'[statusdate]), DAY ),
MIN('Projects'[submissiondate]) > MIN('statuschanges'[statusdate]), DATEDIFF ( MIN('basic'[submissiondate]), MIN('statuschanges'[statusdate]), DAY )* -1)
at the bottom of the screen, I get the message "TABLE: basic (107,846 rows) COLUMN: Days Measure (1 distinct values)"
Any help would be appreciated!
Thanks ~bee
re: "the "Days Measure" column returns a constant 365 for all records":
Double check the relationships and aggregated fields to make sure you're displaying the correct field. Often when a field exists in multiple linked tables it's possible to reference the wrong field, in which case you'll get the same value for all records.
I am trying to use your basic formula, my version is :
Days Last Update = SWITCH ( TRUE (), MIN('Production Approval Workflow'[Status_InFlight])=TRUE, DATEDIFF (MIN('Production Approval Workflow'[Modified]), today(), DAY ), MIN('Production Approval Workflow'[Status_InFlight])=FALSE, "" )
But when I drop it into a table, I get the "Can't display the visual" error.
Note: The field [Modifed] iis a date/time stamp.
Also - why don't the table names and field values prepopulate when I am creating a formula?
Yes, Now it's working fine 🙂 Thank you
What is the correct syntax? Assume I am trying to create a Column and have the number of days between the dates in two columns. DateDiff requires an interval like month, year, day, etc. When I try to create a column using the following
Column = DATEDIFF(Table1[Dates1], Table1[Dates2], DAY)
I get an error that the start date cannot be greater than the end date. I switch the order of dates columns and it still returns the same error.
Even if you have only 1 row where the end_date is before the start_date you'll get an error for the entire column!
So use the above formula and the negative numbers in the results will show you where that error(s) is(are).
Good Luck!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |