Calculating expected income for the selected year

[nandic]

Hi, below is my sample data:

					If selected Year Month = February 2020	month columns below do not exist in model, they are shown to display desired logic
					2019	2019	2019	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2021	2021	2021
Contract ID	Length of Contract (Months)	Contract Start Date	Contract End Date	Contract Monthly Value	1.10.2019	1.11.2019	1.12.2019	1.1.2020	1.2.2020	1.3.2020	1.4.2020	1.5.2020	1.6.2020	1.7.2020	1.8.2020	1.9.2020	1.10.2020	1.11.2020	1.12.2020	1.1.2021	1.2.2021	1.3.2021	Expected Total Amount
7	38	2020-01-01	2023-03-01	160	0	0	0	160	160	160	160	160	160	160	160	160	160	160	160	0	0	0	1.920,00
11	3	2020-01-01	2020-04-01	200	0	0	0	200	200	200	0	0	0	0	0	0	0	0	0	0	0	0	600,00
26	36	2020-01-01	2023-01-01	350	0	0	0	350	350	350	350	350	350	350	350	350	350	350	350	0	0	0	4.200,00
30	36	2020-01-01	2023-01-01	390	0	0	0	390	390	390	390	390	390	390	390	390	390	390	390	0	0	0	4.680,00
8	12	2020-02-01	2021-02-01	170	0	0	0	0	170	170	170	170	170	170	170	170	170	170	170	0	0	0	1.870,00
12	36	2020-02-01	2023-02-01	210	0	0	0	0	210	210	210	210	210	210	210	210	210	210	210	0	0	0	2.310,00
29	23	2020-02-01	2022-01-01	380	0	0	0	0	380	380	380	380	380	380	380	380	380	380	380	0	0	0	4.180,00
19	24	2020-02-24	2022-02-24	280	0	0	0	0	0	280	280	280	280	280	280	280	280	280	280	0	0	0	2.800,00
27	24	2020-02-24	2022-02-24	360	0	0	0	0	0	360	360	360	360	360	360	360	360	360	360	0	0	0	3.600,00
28	2	2020-02-27	2020-04-27	370	0	0	0	0	0	370	370	0	0	0	0	0	0	0	0	0	0	0	740,00
1	22	2020-03-01	2022-01-01	100	0	0	0	0	0	100	100	100	100	100	100	100	100	100	100	0	0	0	1.000,00
21	12	2020-03-01	2021-03-01	300	0	0	0	0	0	300	300	300	300	300	300	300	300	300	300	0	0	0	3.000,00
17	3	2020-03-02	2020-06-02	260	0	0	0	0	0	0	260	260	260	0	0	0	0	0	0	0	0	0	780,00
15	3	2020-03-09	2020-06-09	240	0	0	0	0	0	0	240	240	240	0	0	0	0	0	0	0	0	0	720,00

My goal is to calculate expected yearly income based on year of selected month. I am currently using disconnected date table as based on selected month i need to select all months in year (and later need to display charts with cumulative amounts per month).

Here is the logic: get year of selected month and only do calculations for months in year of selected month.
Example 1: if contract starts on February 2020 and ends in February 2021, calculate only for months in 2020.
Example 2: if contract starts on February 2020 and ends in June 2020, calculate for all months as they are all in 2020.
Example 3: compare start of month to the first of month. If contract start date is on March 1st 2020 then first month to have value is March 2020. If contract start is March 2nd 2020 then start from April 2020.

Final step is to sum all these values (last column on data above "Expected Total Amount").

Notes:
- first five column (heading in green color) exist and they are in one table.
Month columns are used to describe the logic, but i do not have that design so this is not pure If Then Else statement. So these columns should be created virtually or to use some kind of disconnected table.
I used generate function to generate months in measure variable, but something is not working properly on the final output.

Thank you in advance!

Regards,
Nemanja

[v-yangliu-msft]

Hi  @nandic  ,

I convert simulation data into test data according to unpivot columns

Form a new table 2:

Here are the steps you can follow：

1. Create a calculated column in the table

Column =
var _startdate=
IF(DAY([Contract Start Date])=1,[Contract Start Date],DATE(YEAR([Contract Start Date]),MONTH([Contract Start Date])+1,1))
var _enddate=
DATE(YEAR([Contract Start Date]),12,31)
return
CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)',[Date]>=_startdate&&[Date]<=_enddate&&[Contract ID]=EARLIER('Table'[Contract ID])))

2. Result.

You can downloaded PBIX file from here.

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@nandic , Seem like a approch similar to my HR blog should work

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

ot this blog, Just do not divide value by days

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[nandic]

Hi @amitchandak ,
Thank you for provided links, second example is exactly what i needed!
Summarize part of your function works exactly as i wanted (i checked results), it generates row for each month that is in range between start and end date. And i just added one more column which returns value to be summed.
But when i add part of function to sum these values: calculate(sumx(.....)), instead of summing values it only returns value of one row (like it calculates min/max and not sum).
I tested same logic in your file and it works great!

Example in your file:
For ID = 1, there are 14 days and value for each day is 200, so sumx is 14*200=2800.
But in my report instead of 2800 it returns 200. Do you have any idea what might cause this?




On the end i found solution that works which has much more code than your solution (it has generate function, naturalinnerjoin, filters).
Your solution is much more friendly, just to find out why these values are not summed as expected.
In my report there are 2 tables and structure is same as in your file:
 1) data table where i have : id, start date, end date, value
 2) date table: date which is used for filtering

Regards,
Nemanja Andic