Forum Discussion

Caitlin_Knox's avatar
Caitlin_Knox
Advocate III
8 years ago
Solved

Count 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 ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
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's avatar
Caitlin_Knox
Advocate III
8 years ago

This 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's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Glad we got there!