Calculate Duration between Two Dates. And Totals per each month Number during that Duration

[JacktheYeti]

Hello!

I've used DATEDIFF to calculate the number of months (duration) of a campaign, but I'm wondering if it's possible to get the month number from that duration?

For example: If a campaign runs for 5 months, is there a way I can display months 1 - 5 from that duration. 

I'm working on creating a distribution that shows a Sales Campaign data over time (months). My goal is to be able to point to the distribution to illustrate that during the first/middle/last 'x' months of any campaign's duration, 'y' % of sales occured

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[JacktheYeti]

Hello !

The first two tables are examples of data tables lcose to what I'm working with. The Third table is the desired table. Thanks for your help.

Campaign Table

Campaign	Start Date	End Date
Bundle A	1/1/19	2/1/19
Bundle B	5/1/19	10/1/19
Bundle C	3/1/19	7/1/19

Redemption Table

Campaign	Redemption Date	RedemptionCount
Bundle A	1/15/19	2
Bundle A	1/29/19	28
Bundle A	1/30/19	30
Bundle A	2/5/19	20
Bundle A	2/6/19	27
Bundle B	5/1/19	15
Bundle B	5/10/19	15
Bundle B	6/2/19	25
Bundle B	6/5/19	75
Bundle B	7/15/19	24
Bundle B	7/22/19	16
Bundle B	8/10/19	3
Bundle B	9/1/19	1
Bundle C	4/10/19	25
Bundle C	4/12/19	25

Expected Table (Result) 

Campaign	Total Campaign Duration  (Months)	Duration Breakout (Month)	RedemptionCount
Bundle A	2	1	30
Bundle A	2	2	77
Bundle B	5	1	30
Bundle B	5	2	100
Bundle B	5	3	40
Bundle B	5	4	3
Bundle B	5	5	1
Bundle C	3	1	0
Bundle C	3	2	50
Bundle C	3	3	0

[Ashish_Mathur · Accepted solution]

Hi,

The are mistakes in your expected result.  I think the correct result should be what you see in my image below.  You may download my PBI file from here.

Hope this helps.