Forum Discussion
Compare timeseries with different start dates
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))
9 Replies
- amitchandak
Super User
IrisD ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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.
- amitchandak
nda_racgp , Not able to access the file. I tried password you shared
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
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 😞
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
hi, can you share the solution with everyone, please? I'm trying to do something similar
