topic Re: Calculate dates between 2 dates but exclude Start / End date in DAX Commands and Tips

Calculate dates between 2 dates but exclude Start / End date

TMO_KY — Mon, 03 Aug 2020 18:01:04 GMT

I have an interesting ask, I have a code that I use to find dates between 2 given dates;

= Table.AddColumn(#"Renamed Columns", "Work Days", each { Number.From([First Date])..Number.From([Last Date]) })

What I want to do, is extract the dates between the 2 columns (First Date, Last Date) and exclude those given dates. For example if the Start Date is 1/1/2020 and the Last Date is 1/10/2020. I only want to return the dates 1/2/2020-1/9/2020 in my data. Is this possible? Currently it expands all rows which is fine but it includes the start and last date; however these dates are never the same and can change based on project length which is why I can't use any workday formulas. The exlusion doesn't have to be within the same formula, I can always add another step after if that's easier. I'm just stumped on how to make it work.

Re: Calculate dates between 2 dates but exclude Start / End date

TMO_KY — Mon, 03 Aug 2020 18:12:50 GMT

*Update*

So I ended up creating an additional custom column that uses a filter with a true false statement then filtered out the "true's". This works but if anyone has a better workaround with less "steps" I'd appreciate it.

Re: Calculate dates between 2 dates but exclude Start / End date

amitchandak — Mon, 03 Aug 2020 18:30:11 GMT

TMO_KY , refer if this file can help a bit https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

Re: Calculate dates between 2 dates but exclude Start / End date

arq52 — Mon, 03 Aug 2020 22:07:36 GMT

Hello,

use Date.AddDays ;

Table.AddColumn(#"Renamed Columns", "Work Days", each { Number.From(Date.AddDays([First Date],1))..Number.From(Date.AddDays([Last Date],-1)) })

Best regards

Re: Calculate dates between 2 dates but exclude Start / End date

TMO_KY — Tue, 04 Aug 2020 14:07:02 GMT

arq52 Much appreciated! Now I have less steps in my query. 🙂