Calculating end date from start date + duration in months

[Mario_Engel]

Hi,

I have a set of data and I am trying to visualize project income over time using a Line and Stack Column Chart

Deal Id	Amount	Start Date	Duration
A	$ 300	2018/07/31	5
B	$ 500	2018/09/30	3
C	$ 250	2018/06/30	8

The problem I have is to show this recurring over the length of time based on the start date and adding the duration in months to the data. 

The end result I want is something similar to this

Without having to manipulate the data to look like this every month

Deal	Date	Amount
DEAL A	2018/07/31	R  350 000.00
DEAL A	2018/08/31	R  350 000.00
DEAL A	2018/09/30	R  350 000.00
DEAL A	2018/10/31	R  350 000.00
DEAL A	2018/11/30	R  350 000.00
DEAL B	2018/08/31	R  500 000.00
DEAL B	2018/09/30	R  500 000.00
DEAL B	2018/10/31	R  500 000.00
DEAL B	2018/11/30	R  500 000.00
DEAL B	2018/12/31	R  500 000.00
DEAL B	2019/01/31	R  500 000.00
DEAL B	2019/02/28	R  500 000.00
DEAL B	2019/03/31	R  500 000.00

Any advice on how to do this dynamically would be appreciated.

[Anonymous]

Could you please explain how did you derive Table 2 from Table 1 ?

Thanks

Raj

[Mario_Engel]

Hi Raj,

The two are not related at all. I am just showing an example in Table 2 that would be a typical layout in a DB to get a visualization in place.

The Power BI representation is however derived from the data in table two.

[Anonymous]

Could you please post the second table coresponding data for Table 1( Preferabaly in copy/pastabale format)? To answer your qn, this is important to understand how your data to be interpreted.

Thanks

Raj

[Mario_Engel]

Hi Raj,

Please find below. 

The issue is POWER BI is reading off a CRM DB that has the values in single rows like my TABLE 1. I need to know is there any DAX type formula I can use in POWER BI that can give me the below POWER BI result effectively, without having to manipulate the data the whole time to look like my excel sheet attached, cause once it looks like the excel sheet attached its no issue in displaying it, but its impractical to do this the whole time to dynamic data that can change.

Deal ID	Amount	Start Date	Duration
A	$      300.00	2018/07/31	5
A	$      300.00	2018/08/31	5
A	$      300.00	2018/09/30	5
A	$      300.00	2018/10/31	5
A	$      300.00	2018/11/30	5
B	$      500.00	2018/09/30	3
B	$      500.00	2018/10/31	3
B	$      500.00	2018/11/30	3
C	$      250.00	2018/06/30	8
C	$      250.00	2018/07/31	8
C	$      250.00	2018/08/31	8
C	$      250.00	2018/09/30	8
C	$      250.00	2018/10/31	8
C	$      250.00	2018/11/30	8
C	$      250.00	2018/12/31	8
C	$      250.00	2019/01/31	8

Appreciate the guidance.

[Anonymous]

Hi @Mario_Engel

Pls refer this post which has similar requirement.

https://community.powerbi.com/t5/Desktop/loop-Statement-to-DAX/td-p/346048

Thanks

Raj