Distribute Revenue by periods having start date and end date

[Alvarom1]

Hi,

I have a table like this

Contract	Customer	Contract Value	Start Date	End Date
1	a	150.000	16/10/2018	15/01/2019
2	b	1.000.000	11/11/2018	31/03/2019
3	c	360.000	16/11/2018	16/04/2019
4	d	450.000	20/10/2018	31/01/2019
5	a	1.500.000	16/01/2019	27/04/2019
6	b	150.000	01/04/2019	18/05/2019
Total Contract Value	3.610.000

And I want to be able to get a table like this (Group revenue by month...)

Customer	10-18	11-18	12-18	01-19	02-19	03-19	04-19	05-19	Customer total
a	24.725,27	49.450,55	51.098,90	247.497,55	415.841,58	460.396,04	400.990,10	-	1.650.000,00
b	-	135.714,29	221.428,57	221.428,57	200.000,00	221.428,57	92.553,19	57.446,81	1.150.000,00
c	-	33.377,48	73.907,28	73.907,28	66.754,97	73.907,28	38.145,70	-	360.000,00
d	48.058,25	131.067,96	135.436,89	135.436,89	-	-	-	-	450.000,00
Month Total	72.783,53	349.610,28	481.871,65	678.270,30	682.596,55	755.731,90	531.688,99	57.446,81	3.610.000,00

Can you help me to do this?

I have not beenable to figure it ou with the existing related posts

Thanks

Alvaro

[Greg_Deckler]

See if Open Tickets helps. It was designed to do something similar. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Alvarom1]

Thanks Greg

I am afraid I am not getting it.

any chance that you can help me with my data?

Thanks

Alvaro

[Greg_Deckler]

Let me take a look. Why is it that everyone that posts date related questions has the month and day in the wrong order? 😉

[Alvarom1]

Hi again,

looking here and there I came across this solutions that it seems to work. What do you think?

Monthly Value =

VAR Startdate = VALUE(SELECTEDVALUE(datos[Start Date]))

VAR Enddate = VALUE(SELECTEDVALUE(datos[End Date]))

VAR MinDate = VALUE(MIN(Calendario[Date]))

VAR MaxDate = VALUE(MAX(Calendario[Date]))

VAR DaysperContract = DATEDIFF(Startdate;Enddate;DAY)

VAR ContractValue = SUM(datos[Contract Value])

RETURN

IF(and(Startdate<MinDate;Enddate>MinDate);

(MIN(Enddate;MaxDate)-MinDate+1)*(ContractValue/DaysperContract);

IF(AND(AND(Startdate>MinDate;Startdate<MaxDate);Enddate>MinDate);

(MIN(Enddate;MaxDate)-Startdate)*(ContractValue/DaysperContract);

BLANK()))

[amitchandak · Accepted solution]

@Alvarom1 

Please find the solution attached. I do not want to use crossjoin. But seems like the one I created without cross join is not working. Measure 2 is the correct one.

Now there is a difference. That is because I included both the start and end dates to get daily value. So change the daily value as per need.

[Alvarom1]

it works!

fantastic

Thanks a lot

Alvaro