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

My Raw Dates table's data looks like this (SY=School Year, there are two semesters and 4 Terms in the SY):

CalendarDate     SYDayNumber     SYWeekNumber   SYTermNumber   SYSemesterNumber   SY                  Year      Month

8/25/2014          1                           1                            1                          1                                 2014-2015     2015     8          (First Day)

8/26/2014          2                           1                            1                          1                                 2014-2015     2015     8

8/27/2014          3                           1                            1                          1                                 2014-2015     2015     8

.

.

9/8/2014          10                          3                            1                          1                                 2014-2015     2015     9

.

.

10/22/2014       41                          9                            2                          1                                 2014-2015     2015     8

.

6/3/2015           177                        41                          4                          2                                 2014-2015     2015     6 (Last Day)

8/24/2015         1                            1                            1                          1                                 2015-2016     2016     8 (First Day)

.

.

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
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())
                            )
                        )       
                    )
                )
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks, It works, I don't understand yet how, but it does what I needed.

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago
      I'm happy to explain any part you need.