cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eduardo Regular Visitor
Regular Visitor

date issue

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: date issue

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

View solution in original post

5 REPLIES 5

Re: date issue

Hi @eduardo,

 

can you pls share your PBIX file somewhere?

 

Frank

eduardo Regular Visitor
Regular Visitor

Re: date issue

hi @BetterCallFrank

 

Ty for your interest

 

you can get the pbix here

 

best regards

eduardo

 

 

Re: date issue

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

View solution in original post

eduardo Regular Visitor
Regular Visitor

Re: date issue

@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

Highlighted
eduardo Regular Visitor
Regular Visitor

Re: date issue


@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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)