cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Proud to be a Datanaut!

8 REPLIES 8
Microsoft

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

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
I'm happy to explain any part you need.

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

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?

Proud to be a Datanaut!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors