Count of Days Between

[Caitlin_Knox]

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 having I believe is a visualization one. When I put this, using month as my axis- I get the total amount but only based on the Start Date. I would like it to be more of a real representation. The data set is a Scheduled paid time off list. So for example, if someone has requested 90 days off beginning in May- all those days are showing in May instead of dispersed across the 3 months they actually land in. 

[Greg_Deckler]

Create a separate date table that is unrelated to your table. Create a measure that filters ALL fact table between the start and end of the month/week/year/whatever you are putting in the axis. (MIN and MAX of [Date] in your unconnected calendar.

[Caitlin_Knox]

@Greg_DecklerThank you for the reply. I'm having trouble implementing. 

So, I created a separate date table, named DisconnectedDate. I do have a separate fact table, named Formulas. 

Is the MIN and MAX of the DisconnectedDate date field, or of the start date and end date?

[Greg_Deckler]

It is very difficult to conceptualize this and provide a useful answer without sample/example data to recreate. But, generally with these kinds of things, someone is trying to determine if something falls within a specified range and count it. So, they have an axis like "Month". This should come from your date table (calendar table which is your dimension). You then have start date and end date in your fact table. So, the trick is to use MIN and MAX of your [Date] column in your Date/Calendar table. When you have Month in your axis, MIN will be the start of the month and MAX will be the end of the month. You can then create your measure such that it checks the MIN and the MAX and compares that to your start date and end date and do some calculation. So, this might mean that you want only the difference between the start date and the end of the month to show up in "July" if the start date is in the same month as the MIN for July (July 1st). And you want the rest of the time to show up in August (from the MIN of August to the end date because the end date is in August. 

Again, without sample/example source data and an image or something that shows your intended results, extremely difficult to answer other than lay out the theory.

[Caitlin_Knox]

Hi Greg, I wasn't ever able to figure this out. Any chance you have 5 minutes to review the sample file? 

[Greg_Deckler · Accepted solution]

@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]

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]

Glad we got there!

[Greg_Deckler]

@Caitlin_Knox- Taking a look at it now

[Caitlin_Knox]

@Greg_Deckler I feel like I'm almost there, and I really appreciate your time. I put together a sample file, attached here. Hoping you have 5 minutes to review. Thanks again

SampleFile