Datesbetween - Count of Listings across tables

[Locke]

Hi Guys,

I can't seem to figure this out.

Listings

I have a table with listing_id's that have a start_date and end_date. Id's can be listed more than once with different start/end_dates.

Date Table

I have an extra date table with all dates starting 2010.

I need to know for every date how many listings were active -> date between start_date and end_date. I tried to add a column to my date table as follows: 

#Listings = CALCULATE(COUNTA(Listings[SKU]), DATESBETWEEN(Datetable[Date], Listings[StartDay], [Listing End_Day]))

The error message is, that start_day isn't a single value. 

Any ideas, on how I can get the count of active listings for every single day?? Do I need to create anothe table? In my opinion it's just a basic "Check if the date in my column is >start_day and <end_day and give me the number of listings to which this applies".

Really thankful for any help!

Cheers

Locke

[Greg_Deckler]

In your Dates table, add the following column:

#Listings = CALCULATE(COUNT(Listings[ID]),ALL(Listings),FILTER(Listings,[Date]>=[Start] && [Date]<=[End]))

Then you can just display a table visualization with your Dates and #Listings displayed.

[Locke]

Thanks for the quick help!

This seems to at least be counting something. I don't really understand what though. Do I maybe need to use a running total somehow?

[Greg_Deckler]

Shouldn't need a running total. What the formula does is that for each date in your date table, it looks at the date in that row, it counts the ListingID's column (could be any column) by first grabbbing ALL of the Listings, the ALL clause removes all context essentially. Then, it adds back in context to "what listings does the current row date fall in between the start and end date of the listings?" So, it should be doing exactly what you describe here.

[Sean]

Can you provide a sample of what your data table looks like - what columns are involved?

Why don't you just count the SKUs that have no ending date?

[Locke]

There is always an end date, which can be in the future or the past.

listing    start date      end date

book     15.03.2016    19.03.2016

car         18.03.2016    20.03.2016

book      19.03.2016    15.05.2016

table      20.03.2016     15.05.2016

meaning:

15.03.2016 -> 1 listing

...

18.03.2016 -> 2 listings

19.03.2016 -> 3 listings

20.03.2016 -> 3 listings

21.03.2016 -> 2 listings