cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Locke
Regular Visitor

Datesbetween - Count of Listings across tables

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

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?Listingdates.png

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

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
Regular Visitor

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors