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 i...
Need Help
Tips and Tricks
Tutorial Requests
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