Count number of days with two dates (From - To)

[jumiprado]

Hey guys!

I need to take the number of days between 2 dates but per month for example

FROM: 15/03/2018

To: 30/04/2018

i need to receive

Month	Days
March	15
April	30

and a total = 45 days

THANKS!

[sunny_talwar]

You need this in a table with Month as a dimension?

[jumiprado]

i need to create a field to measur the presentism days, today i have a field that returns 30 days for all of the cases, thats wrong about cases like i posted!

Thanks for your question!

[sunny_talwar]

Did you just try To - From + 1 as days

[jumiprado]

yes, but i have this

Per each mont-year of salary settlement i need to count the number of days because the system give me 30 days in all cases and i need count per month-year the number of days

i was clear? Thanks a lot!!

[sunny_talwar]

Not entirely sure I understand

[jumiprado]

i have this expression but not works very well, in some cases i have error

if(left( BASE_Cod_Cambio,1) = 'C',day(f_BASE_Fecha_Desde),

if( f_BASE_Fecha_Desde > FechaAfect AND f_BASE_Fecha_Hasta < MonthEnd( FechaAfect) , DAY(f_BASE_Fecha_Hasta)-DAY(f_BASE_Fecha_Desde)+1,

if( f_BASE_Fecha_Hasta < MonthEnd( FechaAfect), DAY(f_BASE_Fecha_Hasta),

IF(f_BASE_Toma_Posesion < FechaAfect or isnull(f_BASE_Toma_Posesion) and f_BASE_Fecha_Desde < FechaAfect,  '30',

      IF(f_BASE_Toma_Posesion> FechaAfect, 32- DAY(f_BASE_Toma_Posesion))  ))))

And i have other problem to put this on script, because "FechaAfect" is in other table in my model!!

[MarcoWedel]

Hi,

maybe one solution could be:

table1:
LOAD *, RangeMin(To,Floor(MonthEnd(Month)))-RangeMax(From,Month)+1 as Days;
LOAD *, MonthName(From,IterNo()-1) as Month
INLINE [
    From, To
    15/03/2018, 30/04/2018
    10/03/2018, 31/03/2018
    30/04/2018, 01/05/2018
    01/03/2018, 01/06/2018
    30/11/2018, 03/02/2019
] While MonthName(From,IterNo()-1)<=To;

see also:

Number of days per month

hope this helps

regards

Marco