date issue

[eduardo]

First of all: Happy 2017

I'm needing help with basic dates concepts:
I have several campaigns that are repeated throughout the year. Each has a start date and a end date.
ex.
Campaign - Date - Flight - Start Date - End Date
Campaign1 - 10/11/2016 1st flight - 10/10/2016 - 11/30/2016
Campaign1 - 10/12/2016 1st flight - 10/10/2016 - 11/30/2016
Campaign1 - 10/13/2016 1st flight - 10/10/2016 - 11/30/2016
Campaign1 - 10/14/2016 1st flight - 10/10/2016 - 11/30/2016
...
Campaign1 - 11/12/2016 2nd flight - 10/11/2016 - 1/3/2017
Campaign1 - 11/13/2016 2nd flight - 10/11/2016 - 1/3/2017
Campaign1 - 11/14/2016 2nd flight - 10/11/2016 - 1/3/2017
Campaign1 - 11/15/2016 2nd flight - 10/11/2016 - 1/3/2017
...
I need 3 things:
A) Calculation of the number of total days of each campaign
B) Calculation of the time elapsed since the beginning of the campaign

C) Compare CPA of the running days with the same number of days as the previous flight. Example: the current flight has been active for 25 days, I need to compare with the first 25 days of the previous flight

CPA = IF(fDados[.Conversions] = 0 ; [.Investiment]; DIVIDE([.Investiment];[.Conversions];BLANK()))

I created a column for question A)
.daysCol = DATEDIFF (fdados [start_date]; fDados [data_fim]; DAY) +1
Although I bring the correct number, when it comes to applying to a chart I always have to specify that it is a count and not a sum of all the campaigns

I tried to create a measure for Question A)
(DataDate) [d] [COUNTROWS (FILTER (dCalendary; VALUES (fDates [start_date]) <= dCalendar [DataBase] && VALUES (dDate [data_fim])> = dCalendar [DataBase]

But it only works without relationship with the calendar table and gives error when the end date is less than current date.
Do you see another way to do the above?

For Question B) I'm having trouble determining the number of days spent, since it always adds values ​​and that's not what I want.
column:
.DataTest = TODAY () - fDates [start_date]. [Day]

Anyway, I'm screwed and I can not mount these basic measures / columns.
Any charitable soul with the answer?

Best regards
Eduardo

[BetterCallFrank]

Hi @eduardo,

can you pls share your PBIX file somewhere?

Frank

[eduardo]

hi @BetterCallFrank

Ty for your interest

you can get the pbix here

best regards

eduardo

[BetterCallFrank · Accepted solution]

Hi @eduardo,

had a look at your PBIX - i think you should invest some more time into data modelling, i.e. create dimensions for flights/campaigns/redes and so on.

Start and end dates of campaigns are not "clean" i.e. more than one start date for some campaigns etc.

As for A):

storing this information in a calculated column as you did is just fine.

If you want it in a measure you can do it with this DAX

..Duration = CALCULATE( DATEDIFF( MAX( fDados[data_inicio] ),  MAX( fDados[data_fim] ), DAY ))

For column you can define default summarization method to "count" - if thats really what you need, although I'm not sure. If you set this setting to "count" for this column you wont have to change it from "SUM" every time.

As for B)

You can do this with a measure like

..Days Elapsed = DATEDIFF( MAX( fDados[data_inicio] ), TODAY(), DAY )

As for C)

it's not clear to me, what exactly you want to compare -

Running total of CPA could be in a measure like

..RunningCPA = 
VAR MaxDate = MAX( DimDate[Date] )
RETURN
CALCULATE( [.CPA], 
  ALL( DimDate[Date] ),
  DimDate[Date] <= MaxDate
)

but as I said its not entirely clear to me - e.g. in order to determine what is the "previous flight" the flight-dimension would need some kind of "order" or something (look at PBIX file below).

I made some adjustments to your PBIX, you can find it here:

https://dl.dropboxusercontent.com/u/2676210/Mapa%20de%20Campanhas_Novo.pbix

Maybe this helps your understanding a little, let me know where you stand after having a look.

HTH,

Frank

[eduardo]

@BetterCallFrank

Thanks a lot for the help. I will study in detail each of his teachings.

I will post again reporting the result.

Once again, thank you very much.

eduardo

[eduardo]

---

@BetterCallFrank wrote:
but as I said its not entirely clear to me - e.g. in order to determine what is the "previous flight" the flight-dimension would need some kind of "order" or something (look at PBIX file below).

---

Flights have a relationship like table bellow

2017.1 - 2016.1
2016.2 - 2015.2
2016.1 - 2015.1
2017 - 2016
2016 - 2015

best regards

eduardo