Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

time analysis

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

Need Help
Tips and Tricks
Tutorial Requests

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

        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's avatar
    Anonymous
    Not applicable
    7 years ago

    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