Complex Budget Density per Day Calculation

Question

Hello all,&nbsp;thanks for the attention.&nbsp;I am not able to solve a issue I meet up&nbsp;I have the following table A:&nbsp;ProgettoBusiness LineStatusActivity BudgetStart dateEnd DateDelta DaysDensity Budget per days&nbsp;A1KOpen&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15.000 €15/07/202223/09/202270&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 214 €A2XClose&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.000 €09/09/202207/10/202228&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 107 €A3MHold&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.700 €04/11/202201/12/202227&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 63 €A4MOpen&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.000 €30/12/202225/01/202326&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 192 €A5XClose&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.000 €24/02/202321/03/202325&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 400 €A6YHold&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.800 €21/04/202315/05/202324&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 283 €A7YClose&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.000 €16/06/202309/07/202323&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 43 €&nbsp;Delta days : difference between end date and start dateDensity: Activity Budget / Delta days&nbsp;I would like to get a table/mesures that permits to get the density for each days in the following way:&nbsp;for each days of the calendar, it sums the density of all projects if they are started and not ended in that date.&nbsp;I was able to obtain a good results creating a new table (B) with all days and then sum density (Last column) if project is active (between start and end date) using this formula:Density_rev = calculate(sum('A'[Density]),filter('A',and('A'[Start date]&lt;'Date'[Date],'A'[End Date]&gt;'Date'[Date])))but of course in this way I am not able to use the filter from Table A.is there a way to get the same result while still being able to use the filters?&nbsp;I understand it is a very complex question (for me)&nbsp;Many thanks who can advice me&nbsp;GV

lbendlin · Answer

Your sample data is not covering all potential scenarios. Please validate the attached carefully.
&nbsp;

Forum Discussion

Complex Budget Density per Day Calculation

1 Reply

Related content

COMPLEX WORKDAYS CALCULATION

Complex DAX Calculations with Missing Dates

Calculation Group and Complex Measures

Help with complex Measures and Calculated columns

Dynamic Complex Filtering with slicers

Progetto	Business Line	Status	Activity Budget	Start date	End Date	Delta Days	Density Budget per days
A1	K	Open	15.000 €	15/07/2022	23/09/2022	70	214 €
A2	X	Close	3.000 €	09/09/2022	07/10/2022	28	107 €
A3	M	Hold	1.700 €	04/11/2022	01/12/2022	27	63 €
A4	M	Open	5.000 €	30/12/2022	25/01/2023	26	192 €
A5	X	Close	10.000 €	24/02/2023	21/03/2023	25	400 €
A6	Y	Hold	6.800 €	21/04/2023	15/05/2023	24	283 €
A7	Y	Close	1.000 €	16/06/2023	09/07/2023	23	43 €