time analysis

[Anonymous]

i have a start date and an end date, and i am trying to count how many days in each month, have each period, i use DATEDIFF, but this just give me the days for 1 month

in example, the start date is 04/may
the end date is 04/july
and i want to know how many days have the period in may and in july.

hope someone can help me.

thank you

[v-shex-msft]

Hi @Anonymous,

You can try to use following calculated column formula to get detail day count of each year month period.

Detail Count = 
VAR temp =
    ADDCOLUMNS (
        CALENDAR ( [StartDate], [EndDate] ),
        "YearMonth", YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
    )
RETURN
    CONCATENATEX (
        SUMMARIZE (
            temp,
            [YearMonth],
            "Count", COUNTROWS ( FILTER ( temp, [YearMonth] = EARLIER ( [YearMonth] ) ) )
        ),
        [YearMonth] & ":"
            & [Count],
        "|"
    )

Notice: this column is stored text values, if you want to calculate with other fields, you need to do some additional steps to extract numeric value from text.

Regards,

Xiaoxin Sheng

[parry2k]

@Anonymous you have to give internal as "DAY", here is more information about datediff

[Anonymous]

thanks for your reply, but if i  just put day, the result is the days in the time period but i need (in the example) the number of days in may and in july, not the total

[parry2k]

@Anonymous oh in this case you want to know total number of day in may and in june so which will be 31 days for may and 30 days for june, total 61, is this what you are after?

[Anonymous]

yes, thats what im looking for.
thank you

[parry2k]

@Anonymous you can add a column something like this

Total Days = 
DAY( EOMONTH( Table2[Start], 0 ) )  + DAY( EOMONTH( Table2[End], 0 ) )

[Anonymous]

@parry2k thank you, that works for the total days, but didnt  give me the days for each month, I am trying creating columns for each month but i dont get to the correct result

[parry2k]

@Anonymous add two seperae column.

Days for Start Month = 
DAY( EOMONTH( Table2[Start], 0 ) )  


Days for End Month = 
DAY( EOMONTH( Table2[End], 0 ) )

[Anonymous]

@parry2k it didnt work, the expression didnt allow me to select the date column

[parry2k]

@Anonymous not sure why, can you share sample data?