Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DATESYTD in GROUPBY

I have a calculated table with 5 column, and I need to add the 6th one with the number of Days YTD, my table would look like this: Year      School Year    Star Date       End Date      Total Days  ...
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    HI Anonymous

     

    This calculated table might be close.  Give it a try and let me know what you think.

     

    New Table = SUMMARIZECOLUMNS(
    'SchoolDays'[Year],
    'SchoolDays'[SY],
    "Start Date" , MIN('SchoolDays'[CalendarDate]),
    "End Date" , MAX('SchoolDays'[CalendarDate]),
    "Total Days" , COUNTROWS('SchoolDays'),
    "Days YTD" , CALCULATE(
                            COUNTROWS('SchoolDays'),
                            FILTER(ALL('SchoolDays'),
                           'SchoolDays'[Year] = MAX('SchoolDays'[Year]) 
                           && 'SchoolDays'[CalendarDate] < DATE( 
                                                                IF(MONTH(TODAY()) < 8,
                                                                    MAX('SchoolDays'[Year],
                                                                    MAX('SchoolDays'[Year])-1)
                                                                    ),
                                                                MONTH(TODAY()),
                                                                DAY(TODAY())
                            )
                        )       
                    )
                )
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Anonymous,

What do the numbers in Days YTD actually represent?   Why is 2015 = 113, while 2016 = 114?

 

Cheers,

 

Phil

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Phil.

    Those numbers represent the number of  school days, as of today, since the beginning of the school year.

    For instance, this School Year (2017), started on 8/22/2016, as of today (3/13), 116 School Days have passed. This same day last year, 114 school days had passed.

     

    If this is not enough information I can certainly share some of the raw data.

     

    Thanks,

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi Anonymous

       

      And is a school day any Mon to Fri?  Or does this count other holidays?