Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX times series of sum of value between dates

Hi

 

 

I want to create a time series with a value (Sum of RoutesServiced) for every day It should sum the RoutesServiced during the days in which the train station is opened (ie the days between StartDate and ClosureDate)

 

This will then be used for a visualisation to show the total number of routes served nationally on everyday into the future as old stations close and new ones open.

 

I have a Date table which lists everyday between the earliest possible date (ie the first station to open) and the last possible date (ie the last station to close) - there is a relationship between Date and StartDate but I suspect this is not correct, because when I have attempted to use CALCULATE(SUM'Stations'{RoutesServiced], FILTER(Calendar[Date] <='Stations'[StartDate] && Calendar[Date] >= 'Stations'[EndDate]) , it tells me I can't have multiple start dates and need to use Sum, average etc

 

very grateful for help

 

Stations

StationCodeRoutesServicedStartDateClosureDate
Train Station 1201/01/202101/01/2040
Train Station 2301/01/201001/01/2040
Train Station 33401/01/201001/01/2012
Train Station 41201/01/202001/01/2040
Train Station 5101/01/200801/01/2032
Train Station 67601/01/200501/01/2040
Train Station 75301/01/200801/01/2040
Train Station 8501/01/200801/01/2023
Train Station 9801/01/201101/01/2040
Train Station 10901/01/200801/01/2030
Train Station 116701/01/200801/01/2040
Train Station 12201/01/200801/01/2013
Train Station 13401/01/196801/01/2040
Train Station 14201/01/196201/01/2040
Train Station 154501/01/191701/01/2020

 

Calendar

Date
01/01/2018
02/01/2018
03/01/2018
04/01/2018
05/01/2018
06/01/2018
07/01/2018
08/01/2018
09/01/2018
1 ACCEPTED SOLUTION
gooranga1
Power Participant
Power Participant

Hi @Anonymous 

 

You could try create a a New Table from the modelling tab and then entering the following formula;

 

SummaryTable = FILTER(CROSSJOIN('Stations','Date'),Stations[StartDate] <= 'Date'[Date] && Stations[ClosureDate] >= 'Date'[Date])

Gives the below

 

train.PNG

View solution in original post

3 REPLIES 3
gooranga1
Power Participant
Power Participant

Hi @Anonymous 

 

You could try create a a New Table from the modelling tab and then entering the following formula;

 

SummaryTable = FILTER(CROSSJOIN('Stations','Date'),Stations[StartDate] <= 'Date'[Date] && Stations[ClosureDate] >= 'Date'[Date])

Gives the below

 

train.PNG

Anonymous
Not applicable

thanks very much!!!

 

ONe follow up question - I ran it successfully but it took ages (close to 15 minutes) of "working on it" to complete.

 

It is a big dataset (12,000 stations approx) and a long time series (2008 out to 2040)

 

SHould it take as long? Or could it be something wrong with my machine?

If that is just as long as it takes, then I can live with it - will make a cup of tea whenever it needs refreshing

Hi,

 

Yes that is a lot of data! For all stations for one year it's 12,000 * 365 which is 4.5 million rows over 32 years that gets to approx 140 million rows! I am quite impressed it only took 15 minutes. Even if this was stored in a SQL database it would take a while to refresh.

 

Enjoy your tea and possibly a biscuit!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.