Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 ReportingUser | Count |
---|---|
111 | |
105 | |
101 | |
77 | |
44 |
User | Count |
---|---|
144 | |
142 | |
122 | |
100 | |
56 |