cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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,

1 ACCEPTED SOLUTION
Microsoft Employee

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

Proud to be a Datanaut!

8 REPLIES 8
Microsoft Employee

Any chance you can share a small sample of your raw data?

Proud to be a Datanaut!

Anonymous
Not applicable

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)

.

.

Microsoft Employee

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

Proud to be a Datanaut!

Anonymous
Not applicable

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

Microsoft Employee
I'm happy to explain any part you need.

Proud to be a Datanaut!

Microsoft Employee

Hi @Anonymous,

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

Cheers,

Phil

Proud to be a Datanaut!

Anonymous
Not applicable

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,

Microsoft Employee

Hi @Anonymous

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

Proud to be a Datanaut!