Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
StationCode | RoutesServiced | StartDate | ClosureDate |
Train Station 1 | 2 | 01/01/2021 | 01/01/2040 |
Train Station 2 | 3 | 01/01/2010 | 01/01/2040 |
Train Station 3 | 34 | 01/01/2010 | 01/01/2012 |
Train Station 4 | 12 | 01/01/2020 | 01/01/2040 |
Train Station 5 | 1 | 01/01/2008 | 01/01/2032 |
Train Station 6 | 76 | 01/01/2005 | 01/01/2040 |
Train Station 7 | 53 | 01/01/2008 | 01/01/2040 |
Train Station 8 | 5 | 01/01/2008 | 01/01/2023 |
Train Station 9 | 8 | 01/01/2011 | 01/01/2040 |
Train Station 10 | 9 | 01/01/2008 | 01/01/2030 |
Train Station 11 | 67 | 01/01/2008 | 01/01/2040 |
Train Station 12 | 2 | 01/01/2008 | 01/01/2013 |
Train Station 13 | 4 | 01/01/1968 | 01/01/2040 |
Train Station 14 | 2 | 01/01/1962 | 01/01/2040 |
Train Station 15 | 45 | 01/01/1917 | 01/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 |
Solved! Go to Solution.
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |