Compare timeseries with different start dates

[IrisD]

Hi folks, 

I want to compare how different campaigns are progressing based on number of days into the campaign rather than by date (see day1, day2, etc... on the x-axis below).

Here is my DAX code, but I can't get it to work. Any help would be much appreciated...

Normalised Campaign Metrics =

VAR DateReached = CALCULATE(MIN(Days[Day]),db[PAYMENT_DATE]<> BLANK(), KEEPFILTERS(db[PRODUCT_CODE SWITCH]))

VAR MaxDate = CALCULATE(MAX(db[PAYMENT_DATE]),KEEPFILTERS(db[PRODUCT_CODE SWITCH]))

VAR DayNo = SELECTEDVALUE(Days[Day])

RETURN CALCULATE(count(db[PAYMENT_DATE]),
FILTER(ALL(db[PAYMENT_DATE]),
DateReached+DayNo && DateReached+DayNo<=MaxDate))

[adelinadamian]

hi, can you share the solution with everyone, please? I'm trying to do something similar

[Anonymous]

Hi @IrisD @nda_racgp ,

Do you have the [start date] column in the table? If so you could create a calculated column by using DATEDIFF() function to get the date diffenence between [start date] and [PAYMENT_DATE].

days = datediff([start date],[PAYMENT_DATE],days)

Then use this column in your formula and as x-axis in the visual.

Best Regards,

Jay

[nda_racgp]

Hey Jay,

Thanks, we tried this and the axis displays the days, however, it doesn't seem to want to work with a cumulative measure 😞 

[Anonymous]

Hi @nda_racgp ,

You will need to modify the formula to calculate the cumulative value based on the days. Share some fake data so that I might be able to help writing formula.

Best Regards,

Jay

[nda_racgp]

Hi @Anonymous ,

Thanks for your help! Here's some sample data:

Days Since Launch	PRODUCT_CODE SWITCH	Campaign Launch Date	PAYMENT_DATE	Count of PAYMENT_DATE
0	Current Campaign	10/11/2021 0:00	10/11/2021 0:00	1
0	Mar-21	04/03/2021 0:00	04/03/2021 0:00	2
5	Mar-21	04/03/2021 0:00	09/03/2021 0:00	4
6	Mar-21	04/03/2021 0:00	10/03/2021 0:00	127
7	Mar-21	04/03/2021 0:00	11/03/2021 0:00	142
8	Mar-21	04/03/2021 0:00	12/03/2021 0:00	77
9	Mar-21	04/03/2021 0:00	13/03/2021 0:00	31
10	Mar-21	04/03/2021 0:00	14/03/2021 0:00	32
11	Mar-21	04/03/2021 0:00	15/03/2021 0:00	115
12	Mar-21	04/03/2021 0:00	16/03/2021 0:00	56
13	Mar-21	04/03/2021 0:00	17/03/2021 0:00	44
14	Current Campaign	10/11/2021 0:00	24/11/2021 0:00	1
14	Mar-21	04/03/2021 0:00	18/03/2021 0:00	48
15	Current Campaign	10/11/2021 0:00	25/11/2021 0:00	36
15	Mar-21	04/03/2021 0:00	19/03/2021 0:00	25
16	Current Campaign	10/11/2021 0:00	26/11/2021 0:00	47
16	Mar-21	04/03/2021 0:00	20/03/2021 0:00	13
17	Current Campaign	10/11/2021 0:00	27/11/2021 0:00	8
17	Mar-21	04/03/2021 0:00	21/03/2021 0:00	22
18	Mar-21	04/03/2021 0:00	22/03/2021 0:00	73
19	Current Campaign	10/11/2021 0:00	29/11/2021 0:00	43
19	Mar-21	04/03/2021 0:00	23/03/2021 0:00	43
20	Current Campaign	10/11/2021 0:00	30/11/2021 0:00	16
20	Mar-21	04/03/2021 0:00	24/03/2021 0:00	45
21	Current Campaign	10/11/2021 0:00	01/12/2021 0:00	17
21	Mar-21	04/03/2021 0:00	25/03/2021 0:00	59
22	Current Campaign	10/11/2021 0:00	02/12/2021 0:00	13
22	Mar-21	04/03/2021 0:00	26/03/2021 0:00	50
23	Current Campaign	10/11/2021 0:00	03/12/2021 0:00	9
23	Mar-21	04/03/2021 0:00	27/03/2021 0:00	20
24	Current Campaign	10/11/2021 0:00	04/12/2021 0:00	1
24	Mar-21	04/03/2021 0:00	28/03/2021 0:00	25
25	Current Campaign	10/11/2021 0:00	05/12/2021 0:00	1
25	Mar-21	04/03/2021 0:00	29/03/2021 0:00	99
26	Current Campaign	10/11/2021 0:00	06/12/2021 0:00	24
26	Mar-21	04/03/2021 0:00	30/03/2021 0:00	205
27	Current Campaign	10/11/2021 0:00	07/12/2021 0:00	8
27	Mar-21	04/03/2021 0:00	31/03/2021 0:00	225
28	Current Campaign	10/11/2021 0:00	08/12/2021 0:00	15
28	Mar-21	04/03/2021 0:00	01/04/2021 0:00	82
29	Current Campaign	10/11/2021 0:00	09/12/2021 0:00	10
29	Mar-21	04/03/2021 0:00	02/04/2021 0:00	23
30	Current Campaign	10/11/2021 0:00	10/12/2021 0:00	10
30	Mar-21	04/03/2021 0:00	03/04/2021 0:00	11
31	Current Campaign	10/11/2021 0:00	11/12/2021 0:00	2
31	Mar-21	04/03/2021 0:00	04/04/2021 0:00	10

Thank you!

[amitchandak]

@IrisD ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[nda_racgp]

Hey @amitchandak , thanks for getting back to us! I work with @IrisD - are you able to access this link? I'll message you the password.

Campaign performance PBI 

[amitchandak]

@nda_racgp , Not able to access the file. I tried password you shared

[nda_racgp]

Hi @amitchandak , don't think our O365 allows us to share links externally. I've just direct messaged you.

Thanks again!