Forum Discussion
filterable timespan
Der All,
I've got some projects with costs over a timespan (start and end date are given). We assume these costs split linear per month (cost per month= total costs / count of month between start and end):
| project | start | end | cost | cost per month |
| A | 01.07.2021 | 31.05.2024 | 1400000 | 41176.47 |
| B | 15.06.2022 | 20.06.2023 | 9000000 | 750000 |
| C | 01.05.2023 | 30.04.204 | 500000 | 45454.55 |
I have already managed to create a measure which calculates the sum of cost per month. Now I need to create another measure which gives me the sum of the costs for an filterable timespan that can start before the start date, end after and can contain as many months as the user wants to. So i want to calculate the sum over the projects: cost per month * count of month in selected timespan that lay between start and end date
Hi JT317
You can refer to the following example.
The sample data is the same as yours.
First you need to create a date table
e.g
Then create a measure
Measure = var a=ADDCOLUMNS(ALLSELECTED('Table 2'),"Eomonth",EOMONTH([Date],0)) var b=SUMMARIZE(FILTER(a,[Date]>=SELECTEDVALUE('Table'[start])&&[Date]<=SELECTEDVALUE('Table'[end])),[Eomonth]) return COUNTROWS(b)*SUM('Table'[cost per month])Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi JT317
You can refer to the following example.
The sample data is the same as yours.
First you need to create a date table
e.g
Then create a measure
Measure = var a=ADDCOLUMNS(ALLSELECTED('Table 2'),"Eomonth",EOMONTH([Date],0)) var b=SUMMARIZE(FILTER(a,[Date]>=SELECTEDVALUE('Table'[start])&&[Date]<=SELECTEDVALUE('Table'[end])),[Eomonth]) return COUNTROWS(b)*SUM('Table'[cost per month])Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.