I can't seem to figure this out.
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.
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!
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.
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?
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.
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
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
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates