Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

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!

View solution in original post

8 REPLIES 8
Phil_Seamark
Employee
Employee

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)

.

.

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.

 

I'm happy to explain any part you need.

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

Proud to be a Datanaut!

Phil_Seamark
Employee
Employee

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,

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.