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      Days YTD

2015       2014-2015      8/25/2014       6/3/2015        177                  113

2016       2015-2016      8/24/2015       6/2/2016        177                  114

2017       2016-2017      8/22/2016       6/1/2017        178                  116

 

I am using this expression:

School Years = GROUPBY(Dates, Dates[Year], Dates[School Year], "Start Date", MINX( CURRENTGROUP(), Dates[CalendarDate] ), "End Date", MAXX( CURRENTGROUP(), Dates[CalendarDate] ), "Total Days", COUNTX(CURRENTGROUP(), Dates[CalendarDate]), "Days YTD", COUNTX(CURRENTGROUP(), DATESYTD(Dates[CalendarDate])))

 

I get this error:

The CALCULATETABLE function cannot be used in an expression argumane for the GROUPBY() function.

 

How can I achieve what I intend?

 

Thans,

  • 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())
                            )
                        )       
                    )
                )

8 Replies

  • 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?

    • 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
        Icon for Microsoft Employee rankMicrosoft 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())
                            )
                        )       
                    )
                )