custom X-axis

[eduardo]

Hello guys

I have AdWords campaigns and I want compare the first day of the campaign 2015.1 with the first day of campaign 2016.1, the second day with to the second day and so on.

Example Campaign:
2015.1 starts on 07/15/2014 and ends on 05/01/2015
2016.1 starts on 10/08/2015 and ends on 02/15/2016

No problem if a campaign last 100 days and the other last only 80 days.

One way that could show the data was using "day of the year" (for example: 02/02/2016 = day 33, 02/12/2016 = 43 day ...) Unfortunately, this solution does not answer.

Another way is to show a complete date, but this leaves the separate data sets.

My data model is as follows:

I created columns with the names of the campaigns in the data table (fact) and then create the measures

Column:
_2015.1 = If(dados[Conta] = RELATED(IdCampanha[Conta]) && RELATED(IdCampanha[2015.1_I]) <> BLANK() && dados[Dia] >= RELATED(IdCampanha[2015.1_I]) && dados[Dia] <= RELATED(IdCampanha[2015.1_F]);"2015.1";BLANK())

measure:
CPA 2015.1 = CALCULATE([CPA]; dados[_2015.1] = "2015.1")

How can I create the X-axis with day1, day2... day N?

Thank you in advance,

Eduardo

PS. Sorry for the bad English. I also thank the google translator 🙂

edit: images

[v-ljerr-msft]

@eduardo

According to your description, you want to compare same campaign last year(similar to YOY comparisons). Right?

The axis is from the data field in your dataset, you can’t custom axis unless you create a column with all your custom values and put it onto axis. In this scenario, you should still use complete date as X-axis and display two campaigns data together, you can use DATEADD and DATEDIFF function to dynamically get correct days back since two campaigns may have different start date and duration.

The formula below is for your reference.

CPA 2015.1 =
CALCULATE (
    [CPA],
    dados[Conta] = IdCampanha[Conta]
        && IdCampanha[2015.1_I] <> BLANK ()
        && dados[Dia] >= IdCampanha[2015.1_I]
        && dados[Dia] <= IdCampanha[2015.1_F]
)

CPA 2016.1 =
CALCULATE (
    [CPA],
    dados[Conta] = IdCampanha[Conta]
        && IdCampanha[2016.1_I] <> BLANK (),
    DATEADD (
        dados[Dia],
        DATEDIFF (
            MAX ( ALL ( IdCampanha[2015.1_I] ) ),
            MAX ( ALL ( IdCampanha[2016.1_I] ) ),
            DAY
        ),
        DAY
    )
)
 

Regards

[eduardo]

@v-ljerr-msft @CahabaData

ty guys, but I could not solve the problem. 

eduardo

[v-ljerr-msft]

@eduardo

What's your problem now? Could you share the pbix with me? You can upload it to onedrive or dropbox and send me the link in private message. Do mask sensitive data before uploading.

Regards

[eduardo]

@v-ljerr-msft 

I wanna this: 

I got this:

i think the problem will be solved with a new visual not in dataset.

I'll send my dataset and pbix in private message. 

ty sir

eduardo

[eduardo]

I'm back 😛

My last post with this issue.

Can anyone show me the way? the dataset is on dropbox https://www.dropbox.com/s/488pn5fsj08kjec/eduardo.zip?dl=0

thanks

eduardo

[CahabaData]

As I interpret your post, you need a new value.  I will call it: Campaign Day Count

In Campaign 2015.1 it would sequence from 1 on 7/15/14 ....to ... X (whatever is 5/1/15)

You need this in each Campaign.  Then you need to align or join on the Campaign Day Count.

[eduardo]

nobody knows?

eduardo