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.
Hello everyone, I have a question regarding time calculations
I have invoice data with Issue date & Last payment date. Time clients get to pay is standard 1 month. Here is some sample data:
The columns "Paid?" // "OnTime?" // "DaysLate" are calculated columns, the last one is the one I am having problems with.
Solved! Go to Solution.
Use the DATEDIFF() function to return an integer.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDaysLate = datediff(FactHarvest_Invoices[Last Payment Date] , FactHarvest_Invoices[Last Payment Date] , Day)-1
or
DaysLate = datediff(FactHarvest_Invoices[Last Payment Date] , FactHarvest_Invoices[Last Payment Date] , Month)
or
DaysLate = datediff(FactHarvest_Invoices[Last Payment Date] , FactHarvest_Invoices[Last Payment Date] , Day)
Use the DATEDIFF() function to return an integer.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans that seemed to work, any idea how can substract the 1 month payment time tho?
Last Payment date - Issue Date = just the difference between the dates. But I want to know how many days have passed the 1 month payment date, if that makes any sense?
Well, how would you do it in Excel? For example, if the first date is Jan 1 and the 2nd is Mar 15, that is 74 days. How would you add/subtract a month from that? Assume 30 days? You cannot really add a month to an integer as month have 28-31 days. For example, I've always worked with days when dealing with AR agings, not months.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingExcellent @Anonymous - glad you figured out a solution that works!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |