Forum Discussion
Caitlin_Knox
Advocate III
8 years agoCount of Days Between
I have what seems to be a simple problem. I have 2 columns, a Start Date and an End Date. I've created a column to calculate the difference between the 2 using the DATEDIFF function. The problem I'm ...
- 8 years ago
Caitlin_Knox- OK, I believe I have it. I based this on my Quick Measure, Open Tickets here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
You do not want a relationship between your two tables and the formula you want is:
TotalDays = VAR __PTOTable = SELECTCOLUMNS(ScheduledPTO,"StartDate",[NextStartDate],"EndDate",[NextEndDate]) VAR __tmpTable = FILTER( GENERATE( __PTOTable, 'Calendar' ), [Date]>=[StartDate] && [Date]<=[EndDate] ) RETURN COUNTROWS(__tmpTable)BTW, I learned this technique reading @Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence
Well worth the price of admission!
I also attached the PBIX file.
Caitlin_Knox
Advocate III
8 years agoThis was more complicated than I anticipated, but you demonstrated it beautifully. I'm truly grateful.
I've applied the logic to my report in production and it is working just as I expect.
Again, THANK YOU!
Greg_Deckler
Community Champion
8 years agoGlad we got there!