cancel
Showing results for
Search instead for
Did you mean:
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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

8 REPLIES 8
Microsoft

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

To learn more about DAX visit : aka.ms/practicalDAX

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft

Hi @Anonymous,

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

Cheers,

Phil

To learn more about DAX visit : aka.ms/practicalDAX

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

Hi @Anonymous

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

## Helpful resources

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors